Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 6, 2004

Getting the Wrong Identity in Microsoft SQL Server identity Columns? - Page 2

By Don Schlichting

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:



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:


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


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))

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

--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 @@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.


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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

By using this site, you agree to the Privacy Policy