Performance of int vs uniqueidentifier as keys?

[AlterEgo]

Rich,

I'm a big fan of uniqueidentifiers. It really simplifies things when dealing
with horizontally scaled databases. The thing to remember is you will
experience fragmentation if used within a clustered primary key because they
are not inserted into the table in key order as is usually the case with
integers.

If you use uniqueidentifiers, make sure scheduled index rebuilds are part of
your plan.

-- Bill

Good day all,
Just a general question regarding the what the performance hit of using
uniqueidentifiers vs ints for keys is in large hierarchical tables (lots
of
recursive parent/child queries).
There are many advantages to expose an "externally" facing
uniqueidenfitier
key, so is this a huge performance hit, or is it worth maintaining
internal
int keys, and somehow adding additional columns or JOINs for external IDs?
Thanks for any help!
Cheers,
Rich

[Dave Markle]

Also, UniqueIdentifiers are 16 bytes long. So even if they're not used
as a clustered index, your indexes will be about 4 times as big as they
would be with integers. They also tend to sort "funny" (not that you'll
find yourself sorting by them too much):

http://blogs.msdn.com/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx

-Dave

Post by AlterEgo
Rich,
I'm a big fan of uniqueidentifiers. It really simplifies things when dealing
with horizontally scaled databases. The thing to remember is you will
experience fragmentation if used within a clustered primary key because they
are not inserted into the table in key order as is usually the case with
integers.
If you use uniqueidentifiers, make sure scheduled index rebuilds are part of
your plan.
-- Bill

Good day all,
Just a general question regarding the what the performance hit of using
uniqueidentifiers vs ints for keys is in large hierarchical tables (lots
of
recursive parent/child queries).
There are many advantages to expose an "externally" facing
uniqueidenfitier
key, so is this a huge performance hit, or is it worth maintaining
internal
int keys, and somehow adding additional columns or JOINs for external IDs?
Thanks for any help!
Cheers,
Rich

[Erland Sommarskog]

Just a general question regarding the what the performance hit of using
uniqueidentifiers vs ints for keys is in large hierarchical tables (lots
of recursive parent/child queries).
There are many advantages to expose an "externally" facing
uniqueidenfitier key, so is this a huge performance hit, or is it worth
maintaining internal int keys, and somehow adding additional columns or
JOINs for external IDs?

ints are 4 bytes, GUIDs are 16, so that the first effect on performance,
although not dramatic.

As AlterEgo said, GUIDs are prone to cause fragmentation since they are
generated randomly. Make sure your clustered index is not on a GUID
column!

[Simon Sabin]

Hello Rich,

Fragmentation can be reduced by using the new sequential guid function in
SQL 2005.

I appreciate the benefit but the cost is high.

I am a big advocate of clustering the PK, largely due to the reason that
the cluster key is in the leaf of the non-clustered indexes. However with
guid keys this means you have a 16 byte field in your leaf pages. Also be
aware that if you don't have a clustered index you leaf page of your indexes
will contain a RID which is 8 bytes.

Bottom line for me is that space is king, if your db is small this may not
be an issue, however when it grows (which using guids will assist) and is
larger than the memory in your server then having a PK that is 4 times larger
than an int will make a huge difference.


Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

Good day all,
Just a general question regarding the what the performance hit of
using uniqueidentifiers vs ints for keys is in large hierarchical
tables (lots of recursive parent/child queries).
There are many advantages to expose an "externally" facing
uniqueidenfitier key, so is this a huge performance hit, or is it
worth maintaining internal int keys, and somehow adding additional
columns or JOINs for external IDs?
Thanks for any help!
Cheers,
Ric