Reset the value of SQL Server Identity Column

People use the TRUNCATE command in SQL Server to “instantly” delete the contents of a table.

TRUNCATE TABLE MyDatabase.Employees;
GO

It is considered a better way to delete the contents of a table than the DELETE statement, because the DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.  The TRUNCATE statement simply deallocates the data pages and logs that event in the transaction log. This could prevent issues with your transaction log growing during delete actions.

What you may not realize is this also resets the identity column as well. Microsoft says:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Another way to reset the identify column seed value, after to have deleted the records in the table, is to execute this command:

DBCC CHECKIDENT ('MyDatabase.Employees', RESEED, 0);
GO

This will reset the seed value if the table is empty.

Advertisements

1 thought on “Reset the value of SQL Server Identity Column”

  1. Pingback: Ms Sql Updating Identity Column

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