Table variables, identity columns and reseeding

July 25, 2012

Ever tried to reseed an identity column in a table variable? No? Let's give it a go…

Let's declare a simple table variable, @country, and insert a few rows.

DECLARE @country TABLE
(
 CountryId INT IDENTITY(1,1),
 CountryName VARCHAR(50) NOT null
);

INSERT INTO @country
 ( CountryName )
VALUES
 ( 'Scotland' ),
 ( 'England' ),
 ( 'Wales' ),
 ( 'Northern Ireland' ) ;

SELECT
 CountryId ,
 CountryName
FROM @country ;

Truncating a normal table will remove all the rows and reseed the identity value. The following syntax is not valid for a table variable however:

TRUNCATE TABLE @country;

The following error would be returned: Incorrect syntax near ‘@country’.

We can remove all the rows from the table by using DELETE rather than truncate, but this does not reset the identity value. Can we use DBCC CHECKIDENT, like we would on a physical table? No…

DBCC CHECKIDENT (@country, RESEED, 1);

will return Must declare the scalar variable “@country”., and

DBCC CHECKIDENT ([@country], RESEED, 1);

will return Cannot find a table or object with the name “@country”. Check the system catalog.

Table variables cannot be dropped, nor can they be re-declared. In fact, trying to do the latter returns this error: “The variable name ‘@country’ has already been declared. Variable names must be unique within a query batch or stored procedure.“ The bold part of this message is important to note. Bear this in mind while we try to declare the table variable in it's own scope:

DECLARE @i INT = 1,
 @count INT = 5;

WHILE @i <= @count
BEGIN

-- declare table variable in scope of the while loop
 DECLARE @country TABLE
 (
 CountryId INT IDENTITY(1,1),
 CountryName VARCHAR(50) NOT null
 );

INSERT INTO @country
 ( CountryName )
 VALUES
 ( 'Scotland' );

SELECT
 CountryId ,
 CountryName
 FROM @country ;

DELETE FROM @country ;

 SET @i = @i + 1 ;
END

The output is as follows:

reseedinscope

Note that even though we declared the table every time the loop was executed, no error was returned (even though it was being declared in the same batch) and the identity column continued to increment.

So really, this is just an extremely long-winded way of saying…if you need to reseed an identity column and you are using a table variable, consider using a temporary table or a physical table instead!