Checking the identity of a new table for database unit tests

Question

I have a suite of database unit tests that were created. In order to replicate all of the tests on any machine I deploy out to there are scripts to drop the database and recreate it. The test will work for all of the unit tests except for the first test.

The reason the first test fails is that I am executing the "dbcc checkident" command before each test and resetting all of the identities in order to ensure that all of the identities are the same. On a new table that has never been inserted into and then has "dbcc checkident" ran against it the identity starts at 0 when inserted into instead of 1.

If I use some of the other built in commands to check the identity in a fresh table, they return 0 as the identity. The benefit for checking through "dbcc checkident" is that the identity comes back as 'NULL' if a row has never been inserted into it. Only "dbcc checkident" tells us through a print message and cannot easily be tested.

How can I verify that that I need to reset the identity or not through database commands without inserting a row, deleting it, then resetting just to avoid the identity from getting off on the first record?

Example of inserting into a table

Identity after inserting a row on a fresh table without running "dbcc checkident" and setting identity to 0 = 1

Identity after inserting a row on a fresh table and running "dbcc checkident" and setting identity to 0 = 0

Identity after inserting a row on an existing table thats been inserted into and running "dbcc checkident" and setting identity to 0 = 1

Answer 1

To get around the identity insert problem with "dbcc checkident" on a new table, here's what I did.

Using the sys tables I was able to manually check the last identity value through sql. If the last identity was reset, it will change the last value to 0. On a new table that has never been inserted into the last identity will be null.

Here is the code snippet we used. This will allow you to check the last identity without doing an insert, delete, reset identity.

-- {0} is your table name
-- {1} is your identity value of the reset

IF EXISTS (SELECT null FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = '{0}' AND           last_value IS NOT NULL) 
    DBCC CHECKIDENT ({0}, RESEED, {1});