Reseed a newly created table
This behaviour is documented, but counter-intuitive:
Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
It was acknowledged as a bug (on the now-defunct Microsoft Connect site), but not one that will be fixed. Workarounds include avoiding using the RESEED
option on newly-created tables, or to use a higher value that will work in all cases but may result in gaps. Gaps are all but inevitable with IDENTITY
anyway. If you must do the reseed, wrap it in a check like:
IF EXISTS
(
SELECT 1
FROM sys.identity_columns
WHERE
[object_id] = OBJECT_ID(N'dbo.T1', N'U')
AND last_value IS NOT NULL
)
BEGIN
DBCC CHECKIDENT('dbo.T1', 'RESEED', 0)
END;
To be clear, creating a table and adding a row will result in the first identity value being used:
-- Tested on SQL Server 2008 SP3 CU8
-- build 10.0.5828
CREATE TABLE dbo.T1 (id int IDENTITY(1, 1));
INSERT dbo.T1 DEFAULT VALUES;
SELECT id FROM dbo.T1 AS t;
This is happening because you are using 0 for you the new_reseed_value in your DBCC CHECKIDENT command.
See this section from DBCC CHECKIDENT
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.