Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

RESEED in SQL 2000 and SQL 2005/2008

By Raghuram (AJ),

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.

Total article views: 3822 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

How to find unused Stored Procedures in SQL Server 2005/2008

Use dynamic PivotTables to identify unused Stored Procedures in a SQL Server 2005/2008 database

FORUM

SQL clustering 2005/2008 co-exisitance

Any issues with a 2005/2008 x64 clustered single instance server?

BLOG

Owning an Object in SQL Server 2005/2008

One of the things that we have to re-learn when going from SQL Server 2000 to 2005/2008 is that obje...

FORUM

RESEED in SQL 2000 and SQL 2005/2008

Comments posted to this topic are about the item [B]RESEED in SQL 2000 and SQL 2005/2008[/B] good th...

FORUM

Better Excel Insert procedure?

Excel to SQL 2005/2008

Tags
checkident    
identity property    
reseed    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones