How can I reseed an identity column in a T-SQL table variable?

Question

I have a T-SQL table variable (not a table) which has an auto incrementing identity column. I want to clear all data from this variable and reset the identity column value to 1. How can this be done?

Answer 1

Drop, then re-create the table. Repopulate any data you still need from a backup copy.

Answer 2

Truncating the table will dump ALL the data, and reset the identity seed.

Otherwies, you can use this call to reset the identity while retaining any of the data:

DBCC CHECKIDENT (yourtableName, reseed, @NewStartSeedValue)

Answer 3

You don't need to drop and recreate the table. You can just use:

TRUNCATE TABLE [table name]

It will delete all data (this will not be logged) and start the identity count over again.

Answer 4

If you're using a table variable, you can't do it. If it were a table, you could truncate it or use DBCC CHECKIDENT. But, if you have to use a table variable, you have to use something other than an identity column. Or, more accurately, use the identity column in your table variable but output using ROWNUMBER:

DECLARE @t table (pkint int IDENTITY(1,1), somevalue nvarchar(50))
INSERT INTO @t (somevalue) VALUES( 'one')
INSERT INTO @t (somevalue) VALUES('twp')
INSERT INTO @t (somevalue) VALUES('three')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t
DELETE FROM @t
INSERT INTO @t (somevalue) VALUES('four')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t

It's the best you can do with the table variable.