Home Dashboard Directory Help

DBCC CHECKIDENT (@tablevariable, RESEED, 1) - Allow reseed of table variables by DB007


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 324544
Opened: 1/28/2008 3:39:27 AM
Access Restriction: Public


In a nutshell - "Cannot reseed a table variable identity column. Would be nice to be able to"

Code sample to replicate problem:
declare @myfirsttabvariable table (myfirstidentitycol int identity (1,1),
                                    myfirstdatacol varchar(255))
insert into @myfirsttabvariable (
) values ( 'My first row of data' )
insert into @myfirsttabvariable (
) values ( 'My second row of data' )
delete from @myfirsttabvariable
where myfirstidentitycol =2
dbcc checkident (@myfirsttabvariable, RESEED, 1)

-- Generates error: (for the last dbcc line)
--Msg 137, Level 15, State 2, Line 11
--Must declare the scalar variable "@myfirsttabvariable".
Sign in to post a comment.
Posted by Born SQL Server on 3/28/2012 at 10:48 AM
ok so it seems , this feature will not be available 2008, will it be available 2012 ?
Posted by DB007 on 1/30/2008 at 12:52 AM
Posted by Microsoft on 1/29/2008 at 3:30 PM
Thanks for your feedback on allowing a table variable for DBCC checkident call. Since we are at the closing stages of SQL Server 2008, we may not be able to take up this change for this release. However, we will investigate this further and consider this change for a future release of SQL Server.

Srini Acharya
Relational Engine
Posted by DB007 on 1/28/2008 at 3:45 AM
This also affects all ident_current, ident_incr, ident_seed on table variables.

Can make building reusable code units where you wipe the @myfirsttabvariable more complex.
Sign in to post a workaround.
Posted by SAinCA on 1/7/2013 at 1:47 PM
The only way I've managed to do this is to have the TV in a subordinate SP. Each call to the subordinate initializes the TV, including the seed value. Pity the "read-only inside T-SQL" restriction is still there. It would be good to pass a populated TV as an OUTPUT parameter... (Please, Microsoft!)

The requested feature is not in SQL2012.