Home > DBCC, SQL Queries, SQL Server > Changing Identity Seed value of a SQL server table using DBCC

Changing Identity Seed value of a SQL server table using DBCC

September 16, 2009 Leave a comment Go to comments

Whew !! this is my third post in two days on identity fields of SQL server. The more I investigate, more is the information I get.

Most of us , would have faced the problem during deletion of records from a table containing identity column. Deleting records will not reset the value of identity seed. This results in sequential gap between values. Even deleting all the records will not result in identity seed being set to “1”.

You can reset the seed value to your desired value by using DBCC.  Create a table with identity column and insert some records, Now run the query below to get the current Identity Value.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

Now execute the DBCC CHECKIDENT command to reset the Seed value for the table as

DBCC CHECKIDENT('TestTable', RESEED, 33)

check the current Identity Value.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

You will be able to notice a change in the identity value. Be careful while using this, as it can result in duplicates, if your identity column is a non unique column( or PK column).

However , if you are truncating the table ( using TRUNCATE TABLE command) the seed will be automatically set to “1”.

Advertisements
Categories: DBCC, SQL Queries, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: