[sql] DBCC CHECKIDENT Sets Identity to 0

I'm using this code to reset the identity on a table:

DBCC CHECKIDENT('TableName', RESEED, 0)

This works fine most of the time, with the first insert I do inserting 1 into the Id column. However, if I drop the DB and recreate it (using scripts I've written) and then call DBCC CHECKIDENT, the first item inserted will have an ID of 0.

Any ideas?

EDIT: After researching I found out I didn't read the documentation properly - "The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. "

Change statement to

  DBCC CHECKIDENT('TableName', RESEED, 1)

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

I have used this in SQL to set IDENTITY to a particular 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 in C# to set a particular 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 builds on the above answers and always makes sure the next value is 42 (in this case).

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 0);  
GO 



AdventureWorks2012=Your databasename
Person.AddressType=Your tablename

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

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

You are right in what you write in the edit of your question.

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

The solution is in the script below, it's sort of a 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);

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

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

Simply do this:

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

I have used this in SQL to set IDENTITY to a particular 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 in C# to set a particular 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 builds on the above answers and always makes sure the next value is 42 (in this case).

Change statement to

  DBCC CHECKIDENT('TableName', RESEED, 1)

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

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 0);  
GO 



AdventureWorks2012=Your databasename
Person.AddressType=Your tablename

Simply do this:

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

Change statement to

  DBCC CHECKIDENT('TableName', RESEED, 1)

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

Borrowing from Zyphrax's answer ...

USE DatabaseName

DECLARE @ReseedBit BIT = 
    COALESCE((SELECT SUM(CONVERT(BIGINT, ic.last_value))
                FROM sys.identity_columns ic
                INNER JOIN sys.tables t ON ic.object_id = t.object_id), 0)
DECLARE @Reseed INT = 
CASE 
    WHEN @ReseedBit = 0 THEN 1 
    WHEN @ReseedBit = 1 THEN 0 
END

DBCC CHECKIDENT ('dbo.table_name', RESEED, @Reseed);

Caveats: This is intended for use in reference data population situations where a DB is being initialized with enum type definition tables, where the ID values in those tables must always start at 1. The first time the DB is being created (e.g. during SSDT-DB publishing) @Reseed must be 0, but when resetting the data i.e. removing the data and re-inserting it, then @Reseed must be 1. So this code is intended for use in a stored procedure for resetting the DB data, which can be called manually but is also called from the post-deployment script in the SSDT-DB project. In that way the reference data inserts are only defined in one place but aren't restricted to be used only in post-deployment during publishing, they are also available for subsequent use (to support dev and automated test etc.) by calling the stored procedure to reset the DB back to a known good state.

I did this as an experiment to reset the value to 0 as I want my first identity column to be 0 and it's working.

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

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

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

It seems ridiculous that you can't set/reset an identity column with a single command to cover both cases of whether or not the table has had records inserted. I couldn't understand the behavior I was experiencing until I stumbled across this question on SO!

My solution (ugly but works) is to explicitly check the sys.identity_columns.last_value table (which tells you whether or not the table has had records inserted) and call the appropriate DBCC CHECKIDENT command in each case. It is as follows:

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

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

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

It seems ridiculous that you can't set/reset an identity column with a single command to cover both cases of whether or not the table has had records inserted. I couldn't understand the behavior I was experiencing until I stumbled across this question on SO!

My solution (ugly but works) is to explicitly check the sys.identity_columns.last_value table (which tells you whether or not the table has had records inserted) and call the appropriate DBCC CHECKIDENT command in each case. It is as follows:

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

I did this as an experiment to reset the value to 0 as I want my first identity column to be 0 and it's working.

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

You are right in what you write in the edit of your question.

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

The solution is in the script below, it's sort of a 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);