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.

SELECT IDENT_SEED('products') ,will report 10 as our seed number.

SELECT IDENT_INCR('products') ,displays 2 as the increment.

SELECT MAX(IDENTITYCOL) FROM products, reports 18 as the highest identity used.

DBCC CHECKIDENT checks the validity of the identity field, and corrects it if need be, or changes the seed value.

The statement:

DBCC CHECKIDENT (products, NORESEED)

Returns:

Checking identity information: current identity value '18', current column value '18'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The CHECKIDENT statement can accept up to 3 parameters: the table name, reseed, and the optional new reseed number. With NORESEED set, a check will be done without any corrective actions. If there were errors to correct, a value of RESEED would be used. Another benefit of the CHECKIDENT statement is to skip ahead in the identity numbering. If for some reason, we needed new inserts to now begin at 52, the statement and return would be:

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.

SCOPE_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'

@@IDENTITY

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 SCOPE_IDENTITY(). If the identity wanted is for any triggers underneath, use @@IDENTITY.

The third method uses the IDENT_CURRENT function. IDENT_CURRENT requires a parameter for the table name. Like @@IDENTITY, it will look at any scope, but in addition, it will look any session, not just your own.

Conclusion

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.

» See All Articles by Columnist Don Schlichting