Get next primary key without identity

Question

Get next primary key without identity

Viewing 5 posts - 16 through 19 (of 19 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Jeff Moden (8/2/2014)
I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?

Quick thought, transactional behaviour does not alter the SEQUENCE but it can muffle IDENTITY entries, leaving gaps.

😎

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Eirikur Eiriksson (8/2/2014)
Quick thought, transactional behaviour does not alter the SEQUENCE but it can muffle IDENTITY entries, leaving gaps.
😎

No, sequences and IDENTITY behave the same in this regard:

CREATE SEQUENCE MySeq START WITH 1

go

BEGIN TRANSACTION

SELECT NEXT VALUE FOR MySeq -- 1

ROLLBACK TRANSACTION

SELECT NEXT VALUE FOR MySeq -- 2

go

DROP SEQUENCE MySeq

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Eirikur Eiriksson (8/2/2014)
Jeff Moden (8/2/2014)
I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?
Quick thought, transactional behaviour does not alter the SEQUENCE but it can muffle IDENTITY entries, leaving gaps.
😎

Identity uses the SEQUENCE code from 2012 onwards, so behaviourally there's little difference, except that you can specify cache size and other options on a sequence.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Thanks Gail and Erland, I was slightly (understatement) 😀 hasty responding before.

😎

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Erland Sommarskog (8/2/2014)
Jeff Moden (8/2/2014)
I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?
You cannot update an IDENTITY column at all. You can set explicit values on insert, but if you for some reason want to update the values, you have a headache.
Also, if you find out later that you want gaps, and decide to roll your own, you can just drop the default that says NEXT VALUE FOR. Whereas with IDENTITY, you need to do the create-new-table/copy-over/drop-old/rename dance.
With sequences, you don't run the risk to have this pain.

Ah. Thanks, Erland. I'd forgotten about not being able to UPDATE an IDENTITY column. The last time I had to do such a thing was very, very long ago and I did it using a "poor man's update" of DELETE followed by an INSERT. Fortunately, there were no FKs pointing to that column or we'd have had to do the FK "Polka".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)