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 (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 175
Comments posted to this topic are about the item What identity comes next?
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 3722
It was little tough...

Thanks.
Oleg Netchaev
Oleg Netchaev
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: 2746 Visits: 1864
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
murray-906152
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3171 Visits: 287
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
ziangij
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4080 Visits: 380
good one... thanks :-)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168781 Visits: 39530
Had to think this one through, and managed to get it right! Good question!

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)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27493 Visits: 7549
Had to stretch the grey matter on this one.
good question.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Carlo Romagnano
Carlo Romagnano
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10037 Visits: 3466
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:
i v
--- ----
7 A
17 B

(2 row(s) affected)

A check constraint should be used to avoid bad values for identity.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111816 Visits: 13338
Nice question.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SQL Server Youngling
SQL Server Youngling
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 87
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!
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