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.

  • How to INSERT an array of values in SQL Server 2005?
  • SQL Server 2000 Enterprise Manager - Store nvarchar data greater than 4000 characters?
  • MSSQL: How do you script Stored Procedure creation with code?
  • How to check the last time a database table updated
  • Got mired in a selective DELETE statement on a single table in t-SQL
  • Cannot resolve the collation conflict between “SQL_AltDiction_CP850_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation
  • Any ideas?

    EDIT: After researching I found out I didn’t read the documentation properly:
    http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx – “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. “

    8 Solutions collect form web for “DBCC CHECKIDENT Sets Identity to 0”

    As you pointed out in your question it is a documented behavior. I still find it strange though. I use to repopulate the test database and even though I do not rely on the values of identity fields it was a bit of annoying to have different values when populating the database for the first time from scratch and after removing all data and populating again.

    A possible solution is to use truncate to clean the table instead of delete. But then you need to drop all the constraints and recreate them afterwards

    In that way it always behaves as a newly created table and there is no need to call DBCC CHECKIDENT. The first identity value will be the one specified in the table definition and it will be the same no matter if you insert the data for the first time or for the N-th

    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);
    

    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.

    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)

    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)
    
    USE AdventureWorks2012;  
    GO  
    DBCC CHECKIDENT ('Person.AddressType', RESEED, 0);  
    GO 
    
    
    
    AdventureWorks2012=Your databasename
    Person.AddressType=Your tablename
    

    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).

    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 behaviour I was experiencing until I stumbled across the question above 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:

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.