$$SWYNK$$
Replace @@IDENTITY
Yesterday we talked about the difference between the behavior of @@IDENTITY and SCOPE_IDENTITY(), especially as it relates to triggers.
Today I’ll present an option for returning assigned IDENTITY values without relying on global variables or system functions, which are not guaranteed to behave as you may wish.
@@IDENTITY is a global server wide variable returning the last identity value assigned in any table by any connection having completed an insert at the time you read the property.
SCOPY_IDENTITY() is a function returning the last identity value assigned on any table by the process executing the insert statement. Again, it may not return what you expect. As mentioned yesterday, a trigger making an insert statement into another table does not modify the SCOPE_IDENTITY() result.
Using the OUTPUT clause of an INSERT statement always works. In fact, it can be used in a trigger, or anywhere else, and be guaranteed to return exactly what you are looking for. This syntax is specific to SQL Server.
Following is a simple example I create using a temporary table so that you should be able to copy and past it into SQL Server Management Studio without impacting any database.
CREATE TABLE #T (ROWID INT NOT NULL IDENTITY (1,1), Value VARCHAR(10) NOT NULL)
DECLARE @ROWS TABLE (ROWID INT NOT NULL, Value VARCHAR(10) NOT NULL)
INSERT INTO #T
OUTPUT INSERTED.ROWID
,INSERTED.Value
INTO @ROWS
SELECT ‘A’
UNION ALL SELECT ‘B’
UNION ALL SELECT ‘C’
SELECT * FROM @ROWS
- First, I create the Table #T with an IDENTITY column.
- Second, I create a table variable to receive the results of the insert statement.
- Third, I insert three rows into #T
- Fourth, I return the values inserted along with the IDENTITY values assigned in the ROWID column.
One additional benefit of this method is that it works with one or more records, unlike @@IDENTITY or SCOPE_IDENTITY().
Reader Feedback:
George – RE: Getting Rid of @@IDENTITY
I don’t know if you are aware of SQLCop or not. This is a free utility that you can download and run against a SQL Server database. One of the checks in SQLCop is to find all code that uses @@Identity. You can download SQLCop from: http://sqlcop.lessthandot.com
Once you download and run it, expand the code node in the tree to the left of the screen, and then click on “Procedures with @@Identity”. You will see a Wiki article explaining the problem with @@Identity, and you will also see procedures in the DB that use @@Identity.
There’s a lot more that SQLCop will check (other than identity problems). I encourage you to take a look at it.
Michelle –RE: Covering Indexes
This is a great topic…and once the word gets out about covering indexes, everyone who isn’t already at 2008 will be scrambling to get there (don’t I wish?).
I had a situation with a monster report called from a web interface which wasn’t using all that many columns out of the table; at best it took 15 minutes to run, at worst it would simply time out. I built a covering index that included all columns used, and run time dropped to around 1.2- 1.5 minutes, depending on server load . Sweet — and building the index didn’t adversely impact any other application running on the prod server. Extra sweet!
Michael – RE: Getting Rid of ##IDENTITY
Instead of having the hassle with @identity and co. you can use the OUTPUT clause in combination with the INSERT statement instead to fetch the identity value(s) from the target table.
Thanks for the great feedback. Feel send your comments to btaylor@sswug.org.
Cheers,
Ben
Featured Article(s)
Tools to Facilitate Efficient Data Management (Part 2)
The security and integrity of data in production databases is also a prime concern that can be addressed with data management tools.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software
When you’re looking for a reliable tool to diagnose … (read more)