Integer or uniqueidentifier is better

can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:[vbcol=seagreen]
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifie
r
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness
,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as pa
rt
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. Thi
s
> will have some impact on join performance, but it probably will not be tha
t
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...