Reseeding identities in SQL Server

Reseeding identities in SQL Server IDENTITY columns are commonly used as primary keys for SQL Server tables. If you're not familiar with IDENTITY columns, they are automatically incremented columns with numeric data types, such as INT, BIGINT, FLOAT, etc. When you add an IDENTITY column to a table you must specify two values:

1. SEED: the starting value for this column
2. INCREMENT: how much to add to the current identity value to derive the next value.

Both SEED and INCREMENT default to 1, however, you can customize these values to fit your needs. Keep in mind that many data types allow negative values, so you could safely set your identity seed to –1000.

In some cases you'll want to change the identity seed. For instance, suppose you created a database with a few tables and tested your application against it. Now you wish to remove some test records, but you want values in the IDENTITY column to remain sequential, without any gaps. For example, let's suppose that I have a table for storing various types of titles, created as follows:

CREATE TABLE title_type (
 title_type_id INT IDENTITY(1, 1) NOT NULL, 
 type VARCHAR(50) NOT NULL)

During initial testing this table had following values:

title_type_id  type        
-------------  ----------------
1              business    
2              mod_cook    
3              popular_comp
4              psychology  
5              trad_cook   
6              UNDECIDED   

Now that I'm ready to deploy the application in production I no longer need the "UNDECIDED" type. If I delete this title type the next type will be inserted with the identity value of 7.

Of course with a tiny table like this I could easily copy all data to a temporary table and load it back up after truncating the test_type table. By the way, TRUNCATE TABLE command will reset the seed of the identity column to its initial value. However, if you have thousands and millions of records going through, such an exercise is painful.

A simple way to reseed an identity column is to open the table in the design view within Enterprise Manager and change the value of "SEED". However, if you have to reseed 150 tables it might not be such a great idea to click through all of them. Fortunately there is a better way. The DBCC CHECKIDENT command lets you check the current identity value for a particular column, and reseed it if needed.

The syntax for this DBCC command is fairly simple:

DBCC CHECKIDENT ( table_name, [reseed / noreseed], [new_seed_value])

For instance, if I simply want to check the identity value on test_type table, I can execute the following:

DBCC CHECKIDENT ('dbo.test_type', NORESEED)

The output will be similar to the following:

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

If I'd like to change the SEED value in the same table then I can execute the following:

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

Results:

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

One detail to be aware of, however, is that DBCC CHECKIDENT will let you change the identity seed to a value that already exists in the table. For example, if the employee table already has an employee_id of 2, DBCC CHECKIDENT will let you change the seed to 2, even though next time you try to add an employee record you will get the following error:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'Employees'.
The statement has been terminated.

To avoid such pitfalls you should check the maximum value in the identity column prior to reseeding it.

About the Author

Baya Pavliashvili is a MCSE, MCSD and MCDBA with five years experience with SQL Server.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.