Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What identity comes next? Expand / Collapse
Author
Message
Posted Saturday, May 29, 2010 9:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, January 4, 2014 6:22 AM
Points: 1,024, Visits: 175
Comments posted to this topic are about the item What identity comes next?
Post #930099
Posted Saturday, May 29, 2010 11:37 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 932, Visits: 1,524
It was little tough...

Thanks.
Post #930109
Posted Sunday, May 30, 2010 7:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:47 AM
Points: 1,676, Visits: 1,759
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
Post #930201
Posted Sunday, May 30, 2010 9:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, November 8, 2014 2:43 AM
Points: 2,095, Visits: 245
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.).
Post #930217
Posted Monday, May 31, 2010 12:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,099, Visits: 371
good one... thanks
Post #930237
Posted Monday, May 31, 2010 12:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Had to think this one through, and managed to get it right! Good question!



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)
Post #930238
Posted Monday, May 31, 2010 1:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 4,100, Visits: 5,456
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”
Post #930245
Posted Monday, May 31, 2010 1:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:57 AM
Points: 2,587, Visits: 2,443
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.
Post #930251
Posted Monday, May 31, 2010 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #930383
Posted Monday, May 31, 2010 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:04 PM
Points: 13, 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!
Post #930419
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse