When working with SQL Server, it’s common to expect that the behavior of a command will remain consistent across different versions of the product, as long as it is not deprecated. However, there are cases where certain commands may behave differently in different versions. One such command is RESEED, which is used to reset the seed value of an IDENTITY column.
In SQL Server 2000, the behavior of RESEED for virgin tables (tables with no inserts) is different compared to SQL Server 2005/2008. In SQL Server 2000, when you use the RESEED command on a virgin table and then insert a row, the identity column will always increment the seed value. However, in SQL Server 2005/2008, the identity column for virgin tables starts with the new seed value specified in the RESEED command.
Let’s take a look at an example to understand this behavior:
-- 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');
In the above example, the first two inserts set the values of the reseedtestId column to 1 and 2 respectively. Now, let’s truncate the table to make it a virgin table:
-- Truncate the table to make it a virgin table
TRUNCATE TABLE dbo.reseedtest;
-- RESEED on a virgin table
DBCC CHECKIDENT('dbo.reseedtest', RESEED, 0);
After executing the above commands, if we insert a row into the table, we will see different results in SQL Server 2000 and SQL Server 2005/2008:
-- INSERT a row
INSERT dbo.reseedtest (column2) VALUES ('1 in 2000, 0 in 2005/2008');
In SQL Server 2000, the RESEED command always increments the seed value, so the first value to be inserted would be 1. However, in SQL Server 2005/2008, for virgin tables, the identity column starts with the new seed value specified in the RESEED command, so the first value to be inserted would be 0.
This difference in behavior between SQL Server 2000 and SQL Server 2005/2008 is most likely a bug in SQL Server 2000. However, it’s important to be aware of this difference when migrating databases from SQL Server 2000 to SQL Server 2005/2008.
Understanding the behavior of commands like RESEED in different versions of SQL Server is crucial for database administrators and developers to ensure the consistency and accuracy of their data. It’s always recommended to thoroughly test and validate the behavior of commands when migrating databases or working with different versions of SQL Server.