SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by ron.carpenter / What identity comes next? / Latest Posts

RE: What identity comes next?

Melsen Asllani — Wed, 29 Sep 2010 16:35:47 GMT

So, can we say that what SQL Server does to determine the next identityvalue is:[code="sql"]Select Sign(Ident_Incr('tablename')) * Max(Sign(Ident_Incr('tablename')) * $IDENTITY) + Ident_Incr('tablename')From tablename[/code]where [i]$IDENTITY[/i] is the identity column of table [i]tablename[/i] ?

RE: What identity comes next?

Daniel Bowlin — Wed, 02 Jun 2010 07:00:43 GMT

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

RE: What identity comes next?

UMG Developer — Tue, 01 Jun 2010 20:26:16 GMT

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

RE: What identity comes next?

SQLRNNR — Tue, 01 Jun 2010 10:31:39 GMT

Thanks for the question.

RE: What identity comes next?

webrunner — Tue, 01 Jun 2010 09:01:57 GMT

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.[code]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,Temp2IDFROM #Temp1 t1INNER JOIN #Temp2 t2 ON t1.Temp1Value = t2.Temp2ValueWHERE t1.Temp1Value = '4' ;DROP TABLE #Temp1 ;DROP TABLE #Temp2 ;[/code]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

RE: What identity comes next?

Tom Garth — Tue, 01 Jun 2010 08:20:08 GMT

Tricky. I had a 50-50 chance and blew it!Good question. Thanks.

RE: What identity comes next?

Paul White — Tue, 01 Jun 2010 01:13:52 GMT

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:[url]https://connect.microsoft.com/SQLServer/feedback/details/492452/generate-identity-value-is-incorrect-after-set-identity-insert-on-on-an-empty-table[/url]

RE: What identity comes next?

ron.carpenter — Mon, 31 May 2010 14:06:43 GMT

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.

RE: What identity comes next?

SQL Server Youngling — Mon, 31 May 2010 10:12:42 GMT

Three questions:1) I believe the outcome is the most logical, but why doesn't the documentation make mention of the fact the identity will march on irregardless of any identity_insert?http://msdn.microsoft.com/en-us/library/ms186775%28SQL.90%29.aspxhttp://msdn.microsoft.com/en-us/library/ms188059.aspx2) For #Temp1 why was the inital value set to -2147483648, such a crazy large (small) number? Wouldn't -8 have sufficed?3) Do the temporary tables really need to be dropped? Don't they just disappear when the session ends?Thanks for the learning opportunity!

RE: What identity comes next?

Koen Verbeeck — Mon, 31 May 2010 08:06:12 GMT

Nice question.

RE: What identity comes next?

Carlo Romagnano — Mon, 31 May 2010 01:18:55 GMT

If you want an increment of 10, so all values are 0, 10, 20 and so on,be warn of SET IDENTITY_INSERT <table> ONmay lead to unwanted sequence:create table #a(i int not null identity(0,10),v varchar(1))SET IDENTITY_INSERT #a ONINSERT INTO #a ( i ,v )SELECT 7 ,'A'SET IDENTITY_INSERT #a OFFINSERT INTO #a ( v )SELECT 'B'SELECT * FROM #aResult:i v--- ----7 A17 B(2 row(s) affected)A check constraint should be used to avoid bad values for identity.

RE: What identity comes next?

Stewart "Arturius" Campbell — Mon, 31 May 2010 01:00:26 GMT

Had to stretch the grey matter on this one.good question.

RE: What identity comes next?

Lynn Pettis — Mon, 31 May 2010 00:09:59 GMT

Had to think this one through, and managed to get it right! Good question!

RE: What identity comes next?

ziangij — Mon, 31 May 2010 00:06:30 GMT

good one... thanks :-)

RE: What identity comes next?

murray-906152 — Sun, 30 May 2010 21:58:44 GMT

Good question.A better (more explicit) MSDN reference would be:http://msdn.microsoft.com/en-us/library/ms188059(v=SQL.90).aspxThe following extract from the 'Remarks' section covers the issue:'If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.'(Where 'larger' is relative to the direction of the identity.)This extract is better than that provided on the question's Answer's Explanation because it clearly indicates it is the last insert itself that results in the identity value being updated (i.e. not something that is evaluated before the next insert).One can override the setting via IDENT_SEED and then all kinds of problems can be generated (duplicated identity values, etc.).

RE: What identity comes next?

Oleg Netchaev — Sun, 30 May 2010 19:23:18 GMT

This is a great question, thank you Ron!Hats off to the database engine team on this one, the implementation of the seeding next value in the direction of open waters after the identity insert is set back to off is absolutely perfect. This completely prevents the possibility of run-offs.Oleg

RE: What identity comes next?

SQL-DBA-01 — Sat, 29 May 2010 23:37:46 GMT

It was little tough...

What identity comes next?

ron.carpenter — Sat, 29 May 2010 21:05:46 GMT

Comments posted to this topic are about the item [B]What identity comes next?[/B]