Obtaining Identity Column Values in SQL Server

In my last article I discussed identity columns. In that article I discussed one programming issue associated with explicitly setting an identity column while inserting data into a table. In this article I will be expanding the programming discussion to explore other programming considerations when working with identity columns.

Obtaining Current Identity Column Value

When you enter a new record into a table that contains an identity column, the identity value will be set with the next available identity value. Lots of times your database design requires you to obtain the value that was used to set the identity column. The identity value will be used to populate other tables that need to have a foreign key relationship with the inserted record. Therefore you need to be able to return the identity column value of the newly inserted record.

There are a number of different ways to obtain the identity value of a newly inserted record. Let me explore each of the methods and describe how each one works. Let’s first review using the @@IDENTITY function method of returning the identity column value.

The @@IDENTITY is a system function that returns that last inserted identity value. Here is an example of how to use it:


CREATE TABLE MyDescription (
  ID INT IDENTITY(1,1) NOT NULL, 
  MyDescription NVARCHAR (100));
  
INSERT INTO MyDescription VALUES ('Description #1');
SELECT @@IDENTITY;

When I run this code, the @@IDENTITY function returns a value of 1, which is the identity value set on the ID column in the table MyDescription. This is a simple example where only one row was entered into the MyDescription table. Now let’s see how the @@IDENTITY function is affected if multiple rows are inserted into MyDescription table by running the following code:


INSERT INTO MyDescription SELECT TOP 3 Name FROM sys.objects;
SELECT @@IDENTITY;

When I run this code the @@IDENTITY function returns a value of 4. This is because when I insert multiple rows, the value returned from @@IDENTITY will be the value for the identity column of the last row that got inserted.

The @@IDENTITY function is also affected by triggers. To demonstrate this, let’s add a trigger to my table using the following code:


CREATE TABLE MyTriggerDescription (
  ID INT IDENTITY(100,1) NOT NULL,
  MyTriggerDescription NVARCHAR(100));
GO
CREATE TRIGGER MyTrigger
ON MyDescription
AFTER INSERT AS
INSERT INTO MyTriggerDescription VALUES ('Description #100'); 
GO

Once I create the table and trigger, I then run this code to insert another record into MyDescription table:


INSERT INTO MyDescription VALUES ('Description #5');
SELECT @@IDENTITY;

Here the @@IDENTITY function returns 100. This is because the last identity column inserted was performed from within the trigger “MyTrigger” into table “MyTriggerDescription.” The record inserted into this table had a value of 100. Therefore if you are going to using the @@IDENTITY function to obtain the last identity value returned, you better understand which identity value is going to be returned.

An alternative to the @@IDENTITY column is to use SCOPE_IDENTITY. SCOPE_IDENTITY returns that last identity column returned that is in the same scope. By scope I mean the same stored procedure, function, batch, etc. To demonstrate how SCOPE_IDENTITY is different than @@IDENTITY, let’s again run through some more examples. First let me run the following INSERT statement using the MyDescription table that has already been defined and populated above:


INSERT INTO MyDescription VALUES ('Description #6');
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

When I run this code, the @@IDENTITY function returns a value of 101, whereas SCOPE_IDENTITY returns a value of 6. The @@IDENTITY function returned the identity value of the record that was inserted with my trigger “MyTrigger.” The SCOPE_IDENTITY was not affected by the trigger since it ran in a different scope, so the SCOPE_IDENTITY returned the identity value of the record that was inserted into the “MyDescription” table.

Now let me drop the trigger so I can see how SCOPE_IDENTITY is affected when multiple rows are inserted using a single INSERT statement:


DROP TRIGGER MyTrigger;
INSERT INTO MyDescription SELECT TOP 3 Name FROM sys.indexes;
SELECT @@IDENTITY as [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

When running this INSERT statement without the trigger, the @@IDENTITY function and SCOPE_IDENTITY return the same value, 9. Which of course is the identity value of the last row inserted.

As I have demonstrated, SCOPE_IDENTITY is not affected by triggers, or other methods outside the scope of where the SCOPE_IDENTITY method is executed, but yet it still behaves similar to @@IDENTITY when multiple rows are inserting. Once again, while using the SCOPE_IDENTITY to return the last identity value, you need to be aware of what this method will be returning.

There is another method to return the last identity value generated for a table and that is IDENT_CURRENT. Here is an example of how to use IDENT_CURRENT to return the last identity value inserted into “MyDescription” Table:



SELECT IDENT_CURRENT('MyDescription');

When I run this it returns 9, which is similar to what I got above with the @@IDENTITY function and the SCOPE_IDENTITY function. But don’t be fooled by this. IDENT_CURRENT will return the last identity column value for the table identified regardless of the scope. To demonstrate I need to open two different query windows. In query window one I issue the following code:


INSERT INTO MyDescription VALUES ('Description #10');

Then in my query window 2 I issue this statement:


INSERT INTO MyDescription VALUES ('Description #11');

Now I go back to query window #1 and run the following code:


SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY], IDENT_CURRENT('MyDescription');

When I run this code I see the @@IDENTITY and SCOPE_IDENTITY returned a value of 10, whereas IDENT_CURRENT returned an 11. The reason 11 is returned by using the IDENT_CURRENT method is because it returns the last identity value inserted into “MyDescription” table regardless of scope. The statements run in query window 1 and query window 2 are run in different scopes.

As you can see each of these methods to return the current identity value are slightly different and each one is affected differently depending on other factors like triggers, and scope. Depending on what kind of identity value you want to return to your application, you can determine which one of these methods is most appropriate for your situation.

Identity Increment and Seed Values

It is possible that you might want to find out what the identity increment and seed values are for a particular table. In order to identify the increment and seed value you can always look at the table metadata using SQL Server Management Studio. But how can you get at this information programmatically? As it turns out there just happens to be a number of ways to programmatically return the seed and increment values for an identity column.

The first method I will show you is using the IDENT_INCR and IDENT_SEED functions. These functions return the original increment and seed value when the identity column was created. Here is an example of calling these functions to return the seed and increment values for the table “MyTriggerDescription” created in my code earlier in this article:


SELECT IDENT_SEED('MyTriggerDescription') AS SeedValue, 
       IDENT_INCR('MyTriggerDescription') AS IncrementValue;

An alternate method is for you to use the sys.identity_columns catalog view. Using this view provides a wealth of information in addition to just the seed and increment value. To see for yourself what additional information it provides, run the following code against one of your databases that has at least one table with an identity column:


SELECT OBJECT_NAME(object_id), * FROM sys.identity_columns;

Resetting the Seed Value

If you drop all the records in a table or truncate a table that contains an identity column, SQL Server does not automatically reset the seed value to the original seed value when the identity column was created. When records are deleted SQL Server retains the last created identity value. Therefore when additional records are inserted, the identity column values just start incrementing from the retained identity value. If you need to reset the seed value because you have deleted records or truncated all the records, you can do that by using the DBCC CHECKIDENT command.

The DBCC CHECKIDENT command will allow you to reseed a table to an identity value of your choosing. Let me go through a couple examples to show you how this works. First let me create a small script to populate a table with some identity values by running the following script:


CREATE TABLE ReseedTest (ID int IDENTITY(1,1), name varchar(255));
INSERT INTO ReseedTest SELECT TOP 3 name from sys.objects;

Now I will check what the current identity value is by running the following code:


SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

When I run this code I find that the last identity value is 3. Now I run the following statements, which deletes two records, inserts one new record and then displays the identity value of inserted record:


DELETE FROM ReseedTest WHERE ID > 1;
INSERT INTO ReseedTest SELECT TOP 1 name from sys.objects;
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

When I run this code I find that the newly inserted record has an identity value of 4. As you can see SQL Server remembered that 3 was the last identity value inserted. Therefore when I inserted my new row SQL Server set the identity column value to 4.

Now sometimes when I delete a bunch of records like I did above, I want to reseed that table so the next record inserted will have an identity value which is one greater than the current highest identity value. In order to do that I can use the RESEED option of the DBCC CHECKIDENT command like so:


DELETE FROM ReseedTest WHERE ID > 1;
DECLARE @RESEED INT
SELECT @RESEED = MAX(ID) FROM ReseedTest;
DBCC CHECKIDENT('ReseedTest', RESEED, @RESEED)
INSERT INTO ReseedTest SELECT TOP 1 name from sys.objects;
SELECT MAX(ID) FROM ReseedTest;

Here I first deleted all the records that had an identity value greater than 1 from my “ReseedTest” table. Next I set the local variable @RESEED to the maximum identity column value in the table “ReseedTest.” Then I reseeded the “ReseedTest” table based on the @RESEED value. Lastly I inserted a new record into my ReseedTest table and then verified the inserted record got an identity column value of 2.

Manage Your Identity Column Values Programmatically

When developing your database code to correctly populate foreign key relationships that are based on identity columns, it is important that you understand how to correctly obtain the identity value for newly inserted records. Knowing how to obtain the identity column value by using the functions and methods available in SQL Server is critical to making sure your database design hangs together. If data integrity of your database design is important then you better know how to manage your identity column values programmatically.

See all articles by Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles