Click here to monitor SSC
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: 1026 Visits: 175
Comments posted to this topic are about the item What identity comes next?
SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2314 Visits: 3160
It was little tough...

Thanks.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 284
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2905 Visits: 374
good one... thanks :-)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24152 Visits: 37921
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5749 Visits: 7125
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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3583 Visits: 3232
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
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: 16346 Visits: 13199
Nice question.



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

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
SQL Server Youngling
SQL Server Youngling
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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