SQL Server Identity Values Managing and Updating


By:   |   Updated: 2020-07-23   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Identities


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:

results

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.

Checking identity information: current identity value '5', current column value '5'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

Checking identity information: current identity value '5', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

Checking identity information: current identity value '5'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

resutls

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:

Checking identity information: current identity value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

DBCC CHECKIDENT ('dbo.Sales', NORESEED)
Checking identity information: current identity value '100', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

results

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


Last Updated: 2020-07-23


get scripts

next tip button



About the author





Comments For This Article





download





Recommended Reading

SQL Server Identity Insert to Keep Tables Synchronized

Properly Capturing Identity Values in a SQL Server Database

Auto generated SQL Server keys with the uniqueidentifier or IDENTITY

Add or drop identity property for an existing SQL Server column

Populate a SQL Server column with a sequential number not using an identity








get free sql tips
agree to terms


Learn more about SQL Server tools