Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

A Identity Bug

Someone asked an interesting question recently. If they had this code:

if OBJECT_ID('aa') is not null
   drop table aa
create table aa (id int identity(1,1))
go
set identity_insert aa on
insert aa (id) select -10 union select -11
set identity_insert aa off

insert aa default values

select * from aa

drop table aa

The results look like this:

rseults_a

That doesn’t seem right. In fact, I would assume that you would have –10, –11, and 1 as the values. However it doesn’t work.  Why?

It’s a bug. This is actually listed on Connect, and marked as “resolved”, which makes me think that the code has been marked for SQL 11 and corrected.

You can change the identity value. Look at this code:

if OBJECT_ID('aa') is not null
   drop table aa
create table aa (id int identity(1,1))
go
set identity_insert aa on
insert aa (id) select 8
set identity_insert aa off

insert aa default values

select * from aa

drop table aa

It returns 8, and 9, because when you insert a specific value for the identity that is large than the current value, the current value is set to the inserted value.

I’ve used lots of identities, but never messed with negative values, so this surprised me. There’s no reason why you can’t use them, just be aware that if you start an empty table with SET IDENTITY_INSERT on, you could end up skipping the “1".

Comments

Posted by Jan Van der Eecken on 5 July 2011

Interesting behaviour, and yes, certainly not what one would expect. I could replicate that on SQL 2008 R2 RTM, so it is certainly not fixed there. I got a copy of Denali CTP1 somewhere and try what it does there once I get a chance to install it.

Regards,

Jan

Leave a Comment

Please register or log in to leave a comment.