Reset @@identity column - Which one is better

Question

DBCC CHECKIDENT('TableName', RESEED, 0) truncate table dbo.TableName which one is better to reset @@identity column. Lets have a discussion.

Muralidharan Deenathayalan · Answer

If the table has any records, then you can use DBCC CheckIdent otherwise Truncate table is the one. Here you go : TRUNCATE TABLE and DBCC CHECKIDENT Setting the newreseed value below existing values will eventually generate an error if the column is part of a primary key. Use the exact seed value when running DBCC CHECKIDENT against a new table that has never contained data. For instance, a newreseed value of 10 will generate an initial identity value 10. When resetting an empty table (after deleting records), the identity column will generate newreseed + 1. If the next identity value should be 1, use a newreseed value of 0. Deleting records from a table using DELETE doesn't reset an identity column; using TRUNCATE TABLE does. TRUNCATE TABLE is usually faster than DELETE, but in this case, not necessarily the best choice for the job, unless you mean to reset the identity column. Reseeding an identity column is a common task, but you must know the basics or you might get into trouble. Specifically, an identity column can generate duplicate values. Knowing when and why is the key to keeping an identity column in check. DBCC CHECKIDENT (TableName, RESEED, ReseedValue) yields unexpected results when all rows removed from a table http://blogs.x2line.com/al/archive/2003/12/13/158.aspx http://nathondalton.wordpress.com/2010/08/12/delete-table-data-reset-identities-in-sql/ http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/ http://blogs.techrepublic.com.com/datacenter/?p=406 Hope this helps you.

Reset @@identity column - Which one is better

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)