Table variables, identity columns and reseeding
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.