SQL Server Identity Values Managing and Updating

Problem

One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table.  You have the ability to have just one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each record in your table.  Once in awhile you may delete data in the table and want to reset the value or maybe you just want to know what the next number will be.  So how do you figure this out?

Solution

There is a DBCC command that you can use to determine the current value of the identity value or you can use this to reset the value.  The command is: DBCC CHECKIDENT

Setup Test

Let’s setup a test.

CREATE TABLE [dbo].[Sales](
   [salesid] [int] IDENTITY(1,1) NOT NULL,
   [salesPerson] [varchar](50) NULL
) ON [PRIMARY]
-- insert some data
INSERT INTO dbo.Sales (SalesPerson)
SELECT 'Tom' UNION 
SELECT 'William' UNION 
SELECT 'Michael' UNION 
SELECT 'Gary' UNION 
SELECT 'Christine'  
-- select data from table
SELECT * from dbo.Sales

The results are as follows:

Check Current Identity Value for a Table

The first option will show you the current value of the identity column without affecting the value.

DBCC CHECKIDENT ('table_name', NORESEED)

Let’s see what the current value is for table “dbo.Sales”.

DBCC CHECKIDENT ('dbo.Sales', NORESEED)

Here is the information we get for this table.

This shows the current value is 5, so the next value will be 6.  It also shows the current value used in the table is 5.

Reset the Identity Value for a Table

Let’s delete some rows from the table and see what we get.

-- delete some data
DELETE FROM dbo.Sales WHERE salesid IN (4,5)
-- reset the values
DBCC CHECKIDENT ('dbo.Sales', NORESEED)

We get this result.

We can see the current value is 5 which means the next value will be 6, but we will then skip values 4 and 5 since they are no longer in the table.

So we can use the below command to reset the value and make the current identity value = 3.

DBCC CHECKIDENT ('dbo.Sales', RESEED, 3)

We get this information.

So based on the above, did this work?  It still shows the current identity value = 5.

Let’s run the NOSEED command again to see what we get.

DBCC CHECKIDENT ('dbo.Sales', NORESEED)

Here are the results, which looks like it worked.

If we insert a new row, let’s see what value we get.

INSERT INTO dbo.Sales (SalesPerson)
SELECT 'Leah'
SELECT * FROM dbo.Sales

We can see that value 4 was used for the new record, so this worked as intended.

Reset the Identity Value for Table with Specific Starting Value

This last option will reset the identity starting with a new higher value.

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

Let’s change the value to 100 for the table.

DBCC CHECKIDENT ('dbo.Sales', RESEED, 100)

The results:

We can run the NOSEED command again to see what information we get.

DBCC CHECKIDENT ('dbo.Sales', NORESEED)

Let’s insert a new record.

INSERT INTO dbo.Sales (SalesPerson)
SELECT 'Hans'
SELECT * FROM dbo.Sales

Here are the results.  We get a value of 101 for our new record, which is expected since we set the current value to 100.

Conclusion

If there is a need to manage the values in a table, this is one way to go about doing it.  The values are just sequential numbers and don’t really have much meaning, so this isn’t something you should have to do often, but it is good to know this command exists if needed.

Next Steps

• If you were not sure how to manage your identity values take a look at this command and how you can use it to see the value or reset the values if needed
• Check out these related tips:
  • SQL Server Current Identity Value Report for All Tables
  • SQL Server IDENTITY System Function Comparison
  • Properly Capturing Identity Values in a SQL Server Database