

Ten Centuries
Group: General Forum Members
Last Login: Saturday, January 4, 2014 6:22 AM
Points: 1,026,
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.




SSCrazy Eights
Group: General Forum Members
Last Login: Monday, July 27, 2015 4:12 AM
Points: 9,932,
Visits: 11,285





SSC Eights!
Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977,
Visits: 1,499


Tricky. I had a 5050 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




SSCrazy
Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 2,599,
Visits: 3,108


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/sqljoke.html




SSCoach
Group: General Forum Members
Last Login: Today @ 9:40 PM
Points: 18,962,
Visits: 17,247





SSCrazy
Group: General Forum Members
Last Login: Tuesday, June 30, 2015 9:47 AM
Points: 2,160,
Visits: 2,204


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




SSCrazy
Group: General Forum Members
Last Login: Saturday, July 18, 2015 3:29 PM
Points: 2,826,
Visits: 2,601


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




SSC 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 ?




SSCEnthusiastic
Group: General Forum Members
Last Login: Tuesday, January 20, 2015 1:37 AM
Points: 185,
Visits: 123


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




SSCInsane
Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 21,927,
Visits: 34,699


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.
Lynn Pettis
For better assistance in answering your questions, click here For tips to get better help with Performance Problems, click here For Running Totals and its variations, click here or when working with partitioned tables For more about Tally Tables, click here For more about Cross Tabs and Pivots, click here and here Managing Transaction Logs
SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)



