CHECKIDENT DBCC sets identifier 0

Question

I use this code to reset the id in the table:

DBCC CHECKIDENT('TableName', RESEED, 0) 

This works fine in most cases, with the first insertion that I insert 1 into the identifier column. However, if I delete db and recreate it (using written scripts) and then call DBCC CHECKIDENT, the first element inserted will have identifier 0.

Any ideas?

EDIT: After research, it turned out that I did not read the documentation properly: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx - "The current identifier value is set to new_reseed_value. If neither one row has not been inserted into the table since it was created, the first row inserted after DBCC CHECKIDENT will use new_reseed_value as an identifier. Otherwise, the next inserted row will use new_reseed_value + 1. "

Answer 1

As you pointed out in your question, this is documented behavior . It still seems strange to me. I use to refill the test database, and although I do not rely on the values ​​of the identification fields, it was a little annoying to have different values ​​when I first populated the database from scratch and after deleting all the data and refilling.

A possible solution is to use truncate to clear the table instead of deleting it. But then you need to drop all restrictions and then recreate them

Thus, it always behaves like a newly created table, and there is no need to call DBCC CHECKIDENT. The first identifier value will be indicated in the table definition, and it will be the same regardless of whether you insert data for the first time or for the Nth

Answer 2

You are correct in what you write when editing your question.

After starting DBCC CHECKIDENT('TableName', RESEED, 0) :
- Newly created tables will start with identifier 0
- Existing tables will continue with identifier 1

The solution is in the script below, this is kind of poor mans-truncate :)

 -- Remove all records from the Table DELETE FROM TableName -- Use sys.identity_columns to see if there was a last known identity value -- for the Table. If there was one, the Table is not new and needs a reset IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' AND last_value IS NOT NULL) DBCC CHECKIDENT (TableName, RESEED, 0); 

Answer 3

Change the statement to

  DBCC CHECKIDENT('TableName', RESEED, 1) 

This will start at 2 (or 1 when you recreate the table), but it will never be 0.

Answer 4

I did this as an experiment with resetting the value to 0, since I want my first identity column to be 0 and it works.

 dbcc CHECKIDENT(MOVIE,RESEED,0) dbcc CHECKIDENT(MOVIE,RESEED,-1) DBCC CHECKIDENT(MOVIE,NORESEED) 

Answer 5

It seems ridiculous that you cannot set / reset the identifier column with a single command to cover both cases if records were inserted into the table. I could not understand the behavior that I experienced until I came across this question on SO!

My solution (ugly, but works) is to explicitly check the sys.identity_columns.last_value table (which tells you if records are inserted in the table) and call the appropriate DBCC CHECKIDENT in each case. It looks like this:

 DECLARE @last_value INT = CONVERT(INT, (SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'MyTable')); IF @last_value IS NULL BEGIN -- Table newly created and no rows inserted yet; start the IDs off from 1 DBCC CHECKIDENT ('MyTable', RESEED, 1); END ELSE BEGIN -- Table has rows; ensure the IDs continue from the last ID used DECLARE @lastValUsed INT = (SELECT ISNULL(MAX(ID),0) FROM MyTable); DBCC CHECKIDENT ('MyTable', RESEED, @lastValUsed); END 

Answer 6

See also here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/06/26/fun-with-dbcc-chekident.aspx

This is the documented behavior why you run CHECKIDENT if you recreate the table, in this case skip the step or use TRUNCATE (if you don't have FK relationships)

Answer 7

I used this in SQL to set IDENTITY for a specific value: -

 DECLARE @ID int = 42; DECLARE @TABLENAME varchar(50) = 'tablename' DECLARE @SQL nvarchar(1000) = 'IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = '''+@TABLENAME+''' AND last_value IS NOT NULL) BEGIN DBCC CHECKIDENT('+@TABLENAME+', RESEED,' + CONVERT(VARCHAR(10),@ID-1)+'); END ELSE BEGIN DBCC CHECKIDENT('+@TABLENAME+', RESEED,' + CONVERT(VARCHAR(10),@ID)+'); END'; EXEC (@SQL); 

And this is in C # to set a specific value: -

 SetIdentity(context, "tablename", 42); . . private static void SetIdentity(DbContext context, string table,int id) { string str = "IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = '" + table + "' AND last_value IS NOT NULL)\nBEGIN\n"; str += "DBCC CHECKIDENT('" + table + "', RESEED," + (id - 1).ToString() + ");\n"; str += "END\nELSE\nBEGIN\n"; str += "DBCC CHECKIDENT('" + table + "', RESEED," + (id).ToString() + ");\n"; str += "END\n"; context.Database.ExecuteSqlCommand(str); } 

This is based on the answers above and always ensures that the next value is 42 (in this case).

Answer 8

Just do it:

 IF EXISTS (SELECT * FROM tablename) BEGIN DELETE from tablename DBCC checkident ('tablename', reseed, 0) END 

Answer 9

 USE AdventureWorks2012; GO DBCC CHECKIDENT ('Person.AddressType', RESEED, 0); GO AdventureWorks2012=Your databasename Person.AddressType=Your tablename