What identity comes next?

Question

Post reply

What identity comes next?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Another great question, I had to think about this one, but it makes sense. Thanks!

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

Daniel Bowlin

SSC-Dedicated

Points: 34566

June 2, 2010 at 7:00 am

#1175256

I had to puzzle on this one for a while. Thanks.

Melsen Asllani SSC Rookie Points: 38 More actions · Answer 3

So, can we say that what SQL Server does to determine the next identity

value is:

Select Sign(Ident_Incr('tablename')) * Max(Sign(Ident_Incr('tablename')) * $IDENTITY) + Ident_Incr('tablename')

From tablename

where $IDENTITY is the identity column of table tablename ?

BI_NewBie SSCrazy Points: 2261 More actions · Answer 4

Hi,

i have trouble in understanding the solution.

So as we have seen in the first table

CREATE TABLE #Temp1

(

Temp1ID int NOT NULL IDENTITY (-2147483648,1),

Temp1Value char(1) NOT NULL

) ;

INSERT INTO #Temp1 (Temp1Value) VALUES ('1') ;

INSERT INTO #Temp1 (Temp1Value) VALUES ('2') ;

SET IDENTITY_INSERT #Temp1 ON ;

INSERT INTO #Temp1 (Temp1ID,Temp1Value) VALUES (100,'3') ;

SET IDENTITY_INSERT #Temp1 OFF ;

INSERT INTO #Temp1 (Temp1Value) VALUES ('4') ;

after we set identity insert off, the identity value start from 101

but similarly in the second table after identity insert off, still the next value is showing -3 not 101??

what is the difference in these two?

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

BI_NewBie (1/9/2015)
Hi,
i have trouble in understanding the solution.
So as we have seen in the first table
CREATE TABLE #Temp1
(
Temp1ID int NOT NULL IDENTITY (-2147483648,1),
Temp1Value char(1) NOT NULL
) ;
INSERT INTO #Temp1 (Temp1Value) VALUES ('1') ;
INSERT INTO #Temp1 (Temp1Value) VALUES ('2') ;
SET IDENTITY_INSERT #Temp1 ON ;
INSERT INTO #Temp1 (Temp1ID,Temp1Value) VALUES (100,'3') ;
SET IDENTITY_INSERT #Temp1 OFF ;
INSERT INTO #Temp1 (Temp1Value) VALUES ('4') ;
after we set identity insert off, the identity value start from 101
but similarly in the second table after identity insert off, still the next value is showing -3 not 101??
what is the difference in these two?
Thanks

In the second table the identity values are decreasing. This means the next value will be -3 since the identity value you inserted (100) is "less" than the next value to be inserted. I put less in quotes because we know 100 is not actually less than -3.