What identity comes next?

Question

Post reply

What identity comes next?

ron.carpenter

Ten Centuries

Points: 1110
More actions
May 29, 2010 at 9:05 pm

#219527

Comments posted to this topic are about the item What identity comes next?
May 31, 2010 at 1:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

SQL_Hunt

SSC-Dedicated

Points: 33518

May 29, 2010 at 11:37 pm

#1174294

It was little tough...

Thanks.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 2

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

murray-906152 SSCarpal Tunnel Points: 4887 More actions · Answer 3

Good question.

A better (more explicit) MSDN reference would be:

http://msdn.microsoft.com/en-us/library/ms188059(v=SQL.90).aspx

The 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.).

ziangij SSCertifiable Points: 7985 More actions · Answer 4

ziangij

SSCertifiable

Points: 7985

May 31, 2010 at 12:06 am

#1174397

good one... thanks 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 6

If you want an increment of 10, so all values are 0, 10, 20 and so on,

be warn of SET IDENTITY_INSERT <table> ON

may lead to unwanted sequence:

create table #a(i int not null identity(0,10),v varchar(1))

SET IDENTITY_INSERT #a ON

INSERT INTO #a (

i

,v

)

SELECT

7

,'A'

SET IDENTITY_INSERT #a OFF

INSERT INTO #a (

v

)

SELECT

'B'

SELECT * FROM #a

Result:

iv

--- ----

7A

17B

(2 row(s) affected)

A check constraint should be used to avoid bad values for identity.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Nice question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQL Server Youngling Old Hand Points: 360 More actions · Answer 8

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.aspx

http://msdn.microsoft.com/en-us/library/ms188059.aspx

2) 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!

ron.carpenter Ten Centuries Points: 1110 More actions · Answer 9

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.

Paul White SSC Guru Points: 150467 More actions · Answer 10

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
SQLkiwi blog
@SQL_Kiwi

Tom Garth SSCertifiable Points: 6193 More actions · Answer 11

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

Good question. Thanks.

Tom Garth
Vertical Solutions[/url]

"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 SSC-Dedicated Points: 31711 More actions · Answer 12

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

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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[/url]
Learn Extended Events