NewID() vs. Identity field

Question

NewID() vs. Identity field

Barkingdog

SSCoach

Points: 18670
More actions
February 3, 2006 at 11:16 am

#113283

We need a unique value in a table column. The developer prefers to make the column a
uniqueidentifier type and populate it via Newid(). He doesn't like to use incrementing values such as 1,2,3, etc.
His argument is shown below. Do you agree with this or not? Is there a better way?

"As for a next-number-sequence, I just hate that
approach to unique key generation, and would
rather not do it this way. Here's the problem: in
a situation where two processes try to fire that
trigger that needs unique identifiers, if one
grabs the last max id but doesn't write the new
records before another process grabs the same last
max id you are left with bad data since the new id
both processes wrote to the table are the same.
Locking could fix this, but now you are forced to
use explicit locking (not a great proposition when
such a clean option [uuid] is available). I am
not a big fan of UUID myself, but in this
situation I think its the best solution from all
angles."

TIA,

Bill

Viewing 3 posts - 1 through 3 (of 3 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The developer doesn't seem to be arguing about IDENTITY datatypes, but instead seems to be arguing based on the notion that s/he has to SELECT MAX(ID) + 1 As NextID.

Has s/he never heard of IDENTITY datatypes ? Or the waste of 12 bytes per key in a GUID solution ? Or the issues with page splits if the GUID is in a clustered index ?

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Concur... doesn't sound like the Developer ever heard of the IDENTITY property of a column. Using the IDENTITY property does have some other uses, as well... think of it as an automatic rank (as in "seniority") of when records were entered compared to each other, for one...

UNIQUEIDENTIFIERS have the advantage of using consistent lengths and are unique across all databases (and servers, too!). They do take 16 bytes which is twice the number of an INT. Also, no one can tell how many entries you may have because they are not sequential.

--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)