Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

What identity comes next? Expand / Collapse
Author
Message
Posted Monday, May 31, 2010 2:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, January 4, 2014 6:22 AM
Points: 1,024, Visits: 175
Three answers:

1) I do not think that the documentation covers this topic very well, even though it is very intuitive. That is why I posted the question here.

2) Yes, -8 would have sufficed, but I set it to such a crazy large number to demonstrate a point. I always gets points better when they have crazy large numbers in them. :)

3) Yes, the temporary tables end when the session ends. I dropped them because of best practices/force of habit. It's like I tell my children, you have to put away your toys when you are done playing with them.
Post #930468
Posted Tuesday, June 1, 2010 1:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
A very good question. I had to take a guess at whether SQL Server would continue the downward sequence correctly after the IDENTITY_INSERT operation. The correct behaviour is logical, but there have been so many bugs with IDENTITY, it's hard to keep track.

My current favourite example:
https://connect.microsoft.com/SQLServer/feedback/details/492452/generate-identity-value-is-incorrect-after-set-identity-insert-on-on-an-empty-table




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #930552
Posted Tuesday, June 1, 2010 8:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Tricky. I had a 50-50 chance and blew it!

Good question. Thanks.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #930715
Posted Tuesday, June 1, 2010 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 2,330, Visits: 2,664
I had some trouble understanding the explanation of the correct answer at first. The explanation for the answer states, "SQL Server will use the maximum identity value + increment in the direction of the increment for the next identity."

I'm not sure if there is any better way to word it. But I first thought "maximum identity value" was intended to mean 100 in example 2 (for example, SELECT MAX(Temp2ID) FROM #Temp2). Then I realized that the intended meaning was something like, "the value farthest in the direction of the increment value's sign." Or, in terms of SQL: MAX(ID) + 1 if the increment is >= 1, MIN(ID) - 1, if the increment is <= -1.

Here is the sample code with two additional queries I wrote to help me understand it.

CREATE TABLE #Temp1
(
Temp1ID int NOT NULL IDENTITY (-2147483648,1),
Temp1Value char(1) NOT NULL
) ;

CREATE TABLE #Temp2
(
Temp2ID int NOT NULL IDENTITY (-1,-1),
Temp2Value 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 ;
-- Query to show current and next increment values.
SELECT MAX(Temp1ID) AS CURRENT_INCREMENT_VALUE, MAX(Temp1ID) + 1 AS NEXT_INCREMENT_VALUE FROM #Temp1;
INSERT INTO #Temp1 (Temp1Value) VALUES ('4') ;


INSERT INTO #Temp2 (Temp2Value) VALUES ('1') ;
INSERT INTO #Temp2 (Temp2Value) VALUES ('2') ;
SET IDENTITY_INSERT #Temp2 ON ;
INSERT INTO #Temp2 (Temp2ID,Temp2Value) VALUES (100,'3') ;
-- Query to show current and next increment values.
-- Used adding a negative integer to demonstrate "increment" of -1.
SELECT MIN(Temp2ID) AS CURRENT_INCREMENT_VALUE, MIN(Temp2ID) + (-1) AS NEXT_INCREMENT_VALUE FROM #Temp2;
SET IDENTITY_INSERT #Temp2 OFF ;
INSERT INTO #Temp2 (Temp2Value) VALUES ('4') ;

SELECT
Temp1ID,Temp2ID
FROM
#Temp1 t1
INNER JOIN #Temp2 t2
ON t1.Temp1Value = t2.Temp2Value
WHERE
t1.Temp1Value = '4' ;

DROP TABLE #Temp1 ;
DROP TABLE #Temp2 ;

But it is good that SQL Server doesn't work in such a way as to make the wrong answer that I picked (101,99) correct. Otherwise, as others have noted, you would end up with ID collisions when using negative increments.

Thanks,
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #930760
Posted Tuesday, June 1, 2010 10:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #930847
Posted Tuesday, June 1, 2010 8:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Another great question, I had to think about this one, but it makes sense. Thanks!
Post #931063
Posted Wednesday, June 2, 2010 7:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
I had to puzzle on this one for a while. Thanks.
Post #931309
Posted Wednesday, September 29, 2010 4:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:17 PM
Points: 30, Visits: 39
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 ?
Post #995655
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse