TRUNCATE TABLE and DBCC CHECKIDENT

During development, it’s nice to reset the database tables and load the database up to a known state for testing purposes without having to reload the schema.

Well, I was running into a problem where calling DBCC CHECKIDENT() on a newly built database would actually reseed the identity column to the value passed into CHECKIDENT().  For example, after creating a new database (without any previous INSERTs), and calling “DBCC CHECKIDENT(‘table’, reseed, 0)”, the very next INSERT on ‘table’ would cause the identity column on the newly inserted row to be 0 on ‘table’ when it should be 1.

Now, if you had existing rows in ‘table’, deleted the existing rows, then reseeded using “DBCC CHECKIDENT(‘table’, reseed, 0)”, the very next INSERT on ‘table’ would cause the identity column on the newly inserted row to be 1 (as expected).

Whack.  So, calling DBCC on a new database (without any previous inserts) next INSERT identity would be 0, and calling DBCC on a existing database (with previous inserts) next INSERT identity would be 1.  This kind of inconsistent behavior totally defeated my DB tests.  Fortunately, there’s a way to differentiate between newly created tables (with no prior rows) and tables that had have had rows even after a delete.  Here’s a script that I made that completely resets tables and identity columns apart of your build process:(!BE VERY CAREFUL!)

Yes it’s a bit hacky, using undocumented stored procedures and system tables, but it’s the best I could do.  MS doesn’t make this easy…

WARNING THIS WILL COMPLETELY DELETE ANY DATA IN YOUR DATABASE

SET NOCOUNT ON 

Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All' 

Exec sp_MSForEachTable 
' 
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1 
Begin 
    -- Just to know what all table used delete syntax. 
    Print ''Delete from '' + ''?'' 
    Delete From ? 
END 
ELSE BEGIN 
    -- Just to know what all table used Truncate syntax. 
    Print ''Truncate Table '' + ''?'' 
    Truncate Table ? 
END 
' 

Exec sp_MSForEachTable ' 
DECLARE @lastIdentVal sql_variant 

SELECT @lastIdentVal = LAST_VALUE 
    FROM     SYS.IDENTITY_COLUMNS 
    WHERE OBJECT_ID=Object_ID(''?'') 

    If ObjectProperty(Object_ID(''?''), ''TableHasIdentity'')=1 AND @lastIdentVal NOT NULL 
    BEGIN 
        DBCC CHECKIDENT (''?'', reseed, 0) 
        DBCC CHECKIDENT (''?'', reseed) 
    END 
' 

Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'

Hope that helps,

cowboy