Pros & Cons of uniqueidentifier as PK.

Here are some pros and cons of using uniqueidentifier as a PK, do you think
that they justify using or losing it?
Pro
Inserts are evenly spread into the index (clustered or not) and therefore
the index requires less maintenance to remain optimally structured.
Pro
The client can generate the key and so doesnt need a round trip to the
server to get it, simplifying transactional load on the server by reducing
or eliminating the need for nested transactions. (no need to roll back an
inner committed transaction if a child record insert fails because you
havent inserted it yet).
Pro
No need for an extra indexed rowguid for merge replications, you already
have one.
Pro
People cannot guess your primary keys
Con
They can't read them back to you either
Con
when clustered, uses more space in each row on each index on the table,
larger indexes/tables take longer to traverse.
I'm still undecided, what do you think?
Mr Tea
http://mr-tea.blogspot.com> Here are some pros and cons of using uniqueidentifier as a PK, do you
think
> that they justify using or losing it?
You haven't told us anything about your requirements, business needs, usage,
hardware, etc. The pros and cons are laid out for you in many places (e.g.
see http://www.aspfaq.com/2504). It's YOUR job to make YOUR decision. None
of us can make it for you.
Think about reviewing cars at autos.msn.com. Do you want some reviewer to
pick out your car for you? Or do you just want to use his/her opinion as
part of your decision criteria?|||I'm after a few opinions to compliment my own (the faq and related documents
are good reading) although the final decision will always be biased by the
circumstances.
I'm looking for an average table with the normal ratio of
inserts/updates/selects, to attempt to derive which primary key
implementation would put the least load on the server for a given number of
users, and which implementation would handle the most concurrent users
without degrading performance.
This is one dilemma that I have never managed to nail down.
Mr Tea
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OYBJyAOAFHA.3924@.TK2MSFTNGP15.phx.gbl...
> think
> You haven't told us anything about your requirements, business needs,
> usage,
> hardware, etc. The pros and cons are laid out for you in many places
> (e.g.
> see http://www.aspfaq.com/2504). It's YOUR job to make YOUR decision.
> None
> of us can make it for you.
> Think about reviewing cars at autos.msn.com. Do you want some reviewer to
> pick out your car for you? Or do you just want to use his/her opinion as
> part of your decision criteria?
>|||> I'm looking for an average table with the normal ratio of
> inserts/updates/selects, to attempt to derive which primary key
> implementation would put the least load on the server for a given number
of
> users, and which implementation would handle the most concurrent users
> without degrading performance.
> This is one dilemma that I have never managed to nail down.
I don't think you will "nail it down" by having three or four people vote on
it. Have you considered performing load/stress testing and analaysis to
compare the different approaches?|||> Pro
> Inserts are evenly spread into the index (clustered or not) and therefore
> the index requires less maintenance to remain optimally structured.
Actually, this random distribution is a con rather than a pro. This will
increase fragmentation and require more I/O for inserts. Even if you tune
the fillfactor to avoid splits, you'll still incur the cost of reduced
buffer efficiency during inserts with large tables.

> Pro
> People cannot guess your primary keys
Why do you care whether or not people can guess a surrogate primary key
value?
Hope this helps.
Dan Guzman
SQL Server MVP
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:BHBId.1359$3j6.315@.newsfe4-gui.ntli.net...
> Here are some pros and cons of using uniqueidentifier as a PK, do you
> think that they justify using or losing it?
> Pro
> Inserts are evenly spread into the index (clustered or not) and therefore
> the index requires less maintenance to remain optimally structured.
> Pro
> The client can generate the key and so doesnt need a round trip to the
> server to get it, simplifying transactional load on the server by reducing
> or eliminating the need for nested transactions. (no need to roll back an
> inner committed transaction if a child record insert fails because you
> havent inserted it yet).
> Pro
> No need for an extra indexed rowguid for merge replications, you already
> have one.
> Pro
> People cannot guess your primary keys
> Con
> They can't read them back to you either
> Con
> when clustered, uses more space in each row on each index on the table,
> larger indexes/tables take longer to traverse.
> I'm still undecided, what do you think?
> Mr Tea
> http://mr-tea.blogspot.com
>|||> Why do you care whether or not people can guess a surrogate primary key
> value?
I can see this in some cases for the abnormally privacy sensitive, e.g. if I
know my customerID is #3367 and yours is #3389, I know that I was a customer
before you... things along that line.
Deep down, why does it matter? Who knows.
In most cases, you should be exposing the actual unique data that makes a
customer unique (e.g. name, billing address, e-mail address, etc) even if
you are using an artificial key for efficiency or other reasons. I
shouldn't have any clue what my customer ID is.
However, there are cases where that is not a hard and fast rule, either. If
I place 30 orders at buy.com, and I have a problem with one of them, I'd
rather e-mail support with an order number, rather than a big composite
piece of data including my name, dob, billing address, credit card #, e-mail
address and order date. :-)|||Lee Tudor wrote:
> Here are some pros and cons of using uniqueidentifier as a PK, do you
> think that they justify using or losing it?
> Pro
> Inserts are evenly spread into the index (clustered or not) and
> therefore the index requires less maintenance to remain optimally
> structured.
Actually, this causes page splitting and reduced insert throughput if
you cluster on the key. Plus, the UID is 4x larger than a normal INT,
which takes up more space. Used on a clustered index, you've also added
an additional 12 bytes to each non-clustered key.

> Pro
> The client can generate the key and so doesnt need a round trip to the
> server to get it, simplifying transactional load on the server by
> reducing or eliminating the need for nested transactions. (no need to
> roll back an inner committed transaction if a child record insert
> fails because you havent inserted it yet).
>
No roundtrips for identity values required. Your stored procedure that
does the insert just returns the new value using the scope_identity()
function. In fact, you are saving having to send 16 bytes for the UID
when you issue any inserts and pass far less for queries. Plus, you
joins should be faster on a 4-byte key than a 16-byte one.

> Pro
> No need for an extra indexed rowguid for merge replications, you
> already have one.
True.

> Pro
> People cannot guess your primary keys
Does this really matter?

> Con
> They can't read them back to you either
It's a surrogate key, so no one needs to know the value but the system
in most cases.
David Gugick
Imceda Software
www.imceda.com