What identity comes next?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by ron.carpenter » What identity comes next?

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

What identity comes next?

Expand / Collapse

Author

Message

Posted Saturday, May 29, 2010 9:05 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 3:11 AM
Points: 1,023, Visits: 174

Comments posted to this topic are about the item What identity comes next?

Post #930099

Posted Saturday, May 29, 2010 11:37 PM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:33 AM
Points: 738, Visits: 1,126

It was little tough...

Thanks.

Post #930109

Posted Sunday, May 30, 2010 7:23 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:53 AM
Points: 1,662, Visits: 1,709

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

SSCommitted

Group: General Forum Members
Last Login: Thursday, March 28, 2013 12:06 AM
Points: 1,527, Visits: 207

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

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,530, Visits: 359

good one... thanks Smile

Smile

Post #930237

Posted Monday, May 31, 2010 12:09 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 21,627, Visits: 27,477

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

Cool

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)

Post #930238

Stewart "Arturius" Campbell

Posted Monday, May 31, 2010 1:00 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 3,129, Visits: 4,312

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

Carlo Romagnano

Posted Monday, May 31, 2010 1:18 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 1,972, Visits: 1,822

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

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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

SQL Server Youngling

Posted Monday, May 31, 2010 10:12 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 10, 2012 2:14 PM
Points: 13, Visits: 83

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

18 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse