Discovery and Maintenance
There are a few functions to help discover the status of the
,will report 10 as our seed number.
,displays 2 as the increment.
FROM products, reports 18 as the highest identity used.
checks the validity of the identity field, and corrects it if need be, or
changes the seed value.
information: current identity value '18', current column value '18'.
completed. If DBCC printed error messages, contact your system administrator.
statement can accept up to 3 parameters: the table name, reseed, and the
optional new reseed number. With
set, a check will be done without any corrective actions. If there were errors
to correct, a value of
would be used. Another benefit of the
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
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
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
CREATE TABLE test(id int IDENTITY(100,1))
CREATE TRIGGER trigger1 ON products FOR INSERT
INSERT test DEFAULT VALUES
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
INSERT INTO products
--returns 100, the value from the test identity caused by the trigger
--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
The third method uses the
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.
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