Getting the Wrong Identity in Microsoft SQL Server identity Columns? - Page 2
February 6, 2004
Discovery and Maintenance
There are a few functions to help discover the status of the identity field.
DBCC CHECKIDENT (products, RESEED, 50) Checking identity information: current identity value '18', current column value '50'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The next item added would have an identity of 52, the new seed of 50 plus the increment of 2.
Getting the Identity
If you have been working with other databases, the methods for getting SQL identity values may not seem straightforward. In some databases, your auto number field is assigned at the beginning of the insert. In Oracle, if you are using a sequence, you have the identity before you start the insert. With SQL, the new identity is not known until after the insert completes. This leads to problems when you need that new identity to continue with a different transaction, or return the value to your application for future use.
There are three methods for getting the new identity. The difference between them is the scope and session they report on. Use the incorrect one, and the result back may be the wrong identity.
The function SCOPE_IDENTITY returns the new identity created on any table in this session in this scope, giving us the new value we would expect. In this case, a new product will be entered that should have the next identity of 54.
INSERT INTO products (product_name) VALUES ('dvd') SELECT SCOPE_IDENTITY()
Just to confirm:
SELECT * FROM Products where product_name = 'dvd'
This global variable is usually the first one suggested in books and news groups for discovering new identities. However, its real purpose is not at first evident and may produce the wrong identity. @@ IDENTITY will return the last identity used in your session, but across all scopes. Meaning if your statement causes a trigger to fire, and that trigger insert into a table with an identity field, the triggers new identity will be returned, not your statements new identity. For example, create a test table with an identity, and a trigger on the products table that will insert on the test table.
CREATE TABLE test(id int IDENTITY(100,1)) GO CREATE TRIGGER trigger1 ON products FOR INSERT AS BEGIN INSERT test DEFAULT VALUES END GO
Now when an insert on products is done, the trigger will cause an insert on test. After, we will get the new identities using both methods discussed.
INSERT INTO products (product_name) VALUES ('speakers') SELECT @@IDENTITY --returns 100, the value from the test identity caused by the trigger SELECT SCOPE_IDENTITY() --returns 56, from our statement in the products table
The different returns are due to the difference in scopes
examined by each. If you want your identity, use
The third method uses the
Using SQL identity fields provides a quick and effective way of generating unique values. If the correct method is used for retrieving new identities, your application will be assured in not getting the Wrong Identity.