<Denis Gobo’s mode on>
Suppose you have an empty table:
SELECT COUNT(*) FROM SampleTable
---
0
What would be the result of the following query:
DBCC CHECKIDENT('SampleTable', RESEED, 1)
INSERT SampleTable(j) SELECT 1
SELECT SCOPE_IDENTITY()
<Denis Gobo’s mode off>
Surprisingly enough, the answer is “it depends”. If the
table is brand new, you’ll get one, if there have been inserts into the table,
the answer is 2. See for yourself:
CREATE TABLE
SampleTable(i INT
IDENTITY, j INT)
GO
SELECT COUNT(*) FROM SampleTable
---
0
DBCC CHECKIDENT('SampleTable', RESEED, 1)
INSERT SampleTable(j) SELECT 1
SELECT SCOPE_IDENTITY()
---
1
DELETE FROM
SampleTable;
DBCC CHECKIDENT('SampleTable', RESEED, 1);
INSERT SampleTable(j) SELECT 1;
SELECT SCOPE_IDENTITY();
---
2
Even more surprising is the fact that this inconsistent behavior
is by design – it has been documented in MSDN:
http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx
quoting from this MSDN article: “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.”
So far I cannot come up
with a reason why such inconsistent behavior could be required. Any feedback is
welcome.
Anyway, if you are unit testing a stored procedure inserting
into a table with identity, this feature of DBCC CHECKIDENT may break your unit
tests. The workaround that I am using in my database unit testing is simple –
when I set up my test fixture, I run the following script against all the
tables with identities:
BEGIN TRY
INSERT INTO MySchema.MyTable DEFAULT VALUES
END TRY
BEGIN CATCH
PRINT '1'
END CATCH