June 1, 2010 at 8:26 pm
Another great question, I had to think about this one, but it makes sense. Thanks!
June 2, 2010 at 7:00 am
I had to puzzle on this one for a while. Thanks.
September 29, 2010 at 4:35 pm
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 ?
January 9, 2015 at 1:41 am
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
January 9, 2015 at 9:24 am
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.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply