SQL Server Identity Values Managing and Updating

By: Greg Robidoux | Comments | Related: 1 | 2 | 3 | 4 | > 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:

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.

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.

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:

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips