Rudy Rodarte . com

Information

This article was written on 08 Dec 2010, and is filled under SQL Blogs.

Current post is tagged

, ,

Reset an Identity Column in SQL Server

IDENTITY columns are pretty handy since they increment themselves automatically. When you create a table with an IDENTITY column, you can set the seed value and the incremental. That is, you set the column’s initial value and determine how that column grows (by 1, by 10, etc)

So, let’s say you had a table with a seed of 6000, an increment of 1. Lets say that table contains 100 items. You can delete the data in a table with an IDENTITY column with no issues. But when you go to insert new data, you’ll find that the IDENTITY value did not reset. Now, don’t go and drop and recreate the table! Instead, use this handy DBCC command:

DBCC CHECKIDENT (“TABLE_NAME”,RESEED, 6000)

The command above will restart the IDENTITY at the desired number.

You can also check the current IDENTITY value using the same command, but using NORESEED instead:

DBCC CHECKIDENT (“TABLE_NAME”,RESEED, 6000)

Note: Truncating a table will also reset the IDENTITY to the initial seed value.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply