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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 324544 Comments
Status Closed Workarounds
Type Suggestion Repros 0
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 Srini [MSFT] 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.