Reset autoincrement in Microsoft SQL Server 2008 R2

sql-server-2008-r2 auto-increment identity-column
victorio picture victorio · Dec 13, 2012 · Viewed 74.8k times · Source

I created a primary key to be autoincrement.

  • I added two rows: ID=1, ID=2
  • I deleted these two rows.
  • I added a new row, but the new row's ID was: ID=3

How can I reset or restart the autoincrement to 1?

Answer

marc_s picture marc_s · Dec 13, 2012

If you use the DBCC CHECKIDENT command:

 DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

But use with CAUTION! - this will just reset the IDENTITY to 1 - so your next inserts will get values 1, then 2, and then 3 --> and you'll have a clash with your pre-existing value of 3 here!

IDENTITY just dishes out numbers in consecutive order - it does NOT in any way make sure there are no conflicts! If you already have values - do not reseed back to a lower value!

Related questions

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2). I've received "Server is unavailable" errors before, but am now seeing …

Read More
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More