SQL Server and MS BI With a Geek Touch

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″.