RESEED in SQL 2000 and SQL 2005/2008

It's common to expect the results of a command issued in a particular version of a product to not change in the future versions as long as it's not deprecated. However, I bumped into one that behaves differently.

RESEED resets the seed value of the IDENTITY. However, SQL Server 2000 works differently on RESEED for virgin tables when compared with 2005/2008.

Source: 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, 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."

Common usage:

DBCC CHECKIDENT(table_name, RESEED, new_value);

The above command is read as: For the table_name, reset the seed value of the identity column to new_value.

Let's test it.

--Create table
CREATE TABLE dbo.reseedtest (reseedtestId INT IDENTITY(1,1), column2 NVARCHAR(50));

--Insert a few rows
INSERT dbo.reseedtest (column2) VALUES ('One')
INSERT dbo.reseedtest (column2) VALUES ('Two')

The above 2 inserts sets the values 1 & 2 to reseedtestId. Before we proceed, we need to know what a virgin table is. A virgin table is a table that has had NO inserts on it since it has been created. It's an absolutely fresh table.

Note: If a table already has rows, "DELETE FROM table_name" does not make it a virgin table, but, "TRUNCATE TABLE table_name" does! Truncate reseeds the identity column automatically.

Now, let's use the RESEED command.

--RESEED on a non-virgin table and insert a few rows
DBCC CHECKIDENT('dbo.reseedtest',RESEED,3);

INSERT dbo.reseedtest (column2) VALUES ('Three')
INSERT dbo.reseedtest (column2) VALUES ('Four')

The above 2 inserts result in reseedtestId column for the new rows to have 4 & 5 due to the reseed command. So far, there's no change in how SQL 2000 or 2005/2008 behave for the above set of commands.

Let's see where it differs for RESEED. Truncate and reseed to 0.

--Truncate the table to make it a virgin table
TRUNCATE TABLE dbo.reseedtest

--RESEED on a virgin table
DBCC CHECKIDENT('dbo.reseedtest',RESEED,0);

Now upon an insert into this table, we find different results for SQL 2000 and SQL 2005/2008.

--INSERT a row
INSERT dbo.reseedtest (column2) VALUES ('1 in 2000, 0 in 2005/2008')

The differences can be seen as:

SQL Server 2000:
RESEED always increments the seed value.

SQL Server 2005/2008:
For virgin tables, it doesn't increment but starts with the new_value. So in the above case, the first value to be inserted would be 0 and not 1.

I wonder why a new version should give different results for the same command with the same parameters and no extra options. It was most likely a bug in SQL Server 2000. In any case, this could be something on the checklist during a database's migration from 2000 to 2005/2008.