DBCC CHECKIDENT Sets Identity to 0

Question

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 -  quot 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   quot

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

USE AdventureWorks2012    GO   DBCC CHECKIDENT   Person AddressType   RESEED  0     GO     AdventureWorks2012 Your databasename Person AddressType Your tablename

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

USE AdventureWorks2012    GO   DBCC CHECKIDENT   Person AddressType   RESEED  0     GO     AdventureWorks2012 Your databasename Person AddressType Your tablename

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Simply do this   IF EXISTS  SELECT   FROM tablename  BEGIN     DELETE from  tablename     DBCC checkident   tablename   reseed  0  END

User · Answer

Simply do this   IF EXISTS  SELECT   FROM tablename  BEGIN     DELETE from  tablename     DBCC checkident   tablename   reseed  0  END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] DBCC CHECKIDENT Sets Identity to 0

Examples related to sql

Examples related to sql-server