SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What identity comes next?


What identity comes next?

Author
Message
ron.carpenter
ron.carpenter
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 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. Smile

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.
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16304 Visits: 11355
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 1499
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

webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 3887
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. w00t Otherwise, as others have noted, you would end up with ID collisions when using negative increments.

Thanks,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33328 Visits: 18560
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2732 Visits: 2204
Another great question, I had to think about this one, but it makes sense. Thanks!
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 2629
I had to puzzle on this one for a while. Thanks.
Melsen Asllani
Melsen Asllani
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 51
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
BI_NewBie
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 157
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
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40752 Visits: 38567
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search