Posted by: osmosee | November 3, 2010

Resetting the Auto Increment counter in MS SQL Server

We are working on a big enterprise solution where many tables, Stored Procedures, and Triggers are used in MS SQL Server. Most of the tables have “Auto Increment” columns used as Primary Key columns.
It so happens that once in a while we want to clean data in a table, but when we enter new details, PK value starts from the MAX(). This poses serious problems for us as some of the values are already in use by other tables. So, once we delete all the records in the table we reset the counter back to zero by running the below command:

DBCC CHECKIDENT('<Table Name>', RESEED, <COUNTER Value>) ;

“Table Name” has to be replaced by the actual table name and the “COUNTER Value” has to be replaced with the increment value desired. For example, if you want to make Auto Increment value start from 9 in Employee table, SQL statement should look like the below:

 
DBCC CHECKIDENT('employee', RESEED,8) ;

‘8’ in the above denotes the MAX (Auto Increment Column value). When a new record is inserted in the employee table, that record will have the ID 9 (8 + 1).

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

Join 45 other followers

%d bloggers like this: