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'
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1
-- Just to know what all table used delete syntax.
Print ''Delete from '' + ''?''
Delete From ?
-- Just to know what all table used Truncate syntax.
Print ''Truncate Table '' + ''?''
Truncate Table ?
Exec sp_MSForEachTable '
DECLARE @lastIdentVal sql_variant
SELECT @lastIdentVal = LAST_VALUE
If ObjectProperty(Object_ID(''?''), ''TableHasIdentity'')=1 AND @lastIdentVal NOT NULL
DBCC CHECKIDENT (''?'', reseed, 0)
DBCC CHECKIDENT (''?'', reseed)
Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'
Hope that helps,