Identity or GUID?

OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
RobertHi
http://www.sql-server-performance.c...red_indexes.asp
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:eZPZpnWiGHA.428
4@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
Robert|||From what I have read and understand Indexes which gets built on GUID wud be
pretty heavy and so it might have a performance hit.
http://www.thescripts.com/forum/thread82632.html -- this might help.
Best Regards
Vadivel
http://vadivel.blogspot.com
"rmg66" wrote:

> OK Guys,
> We are considering replacing the standard IDENTITY column with the UNIQUEI
DENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
> Robert
>|||Hi Robert,
Why? The only reason I can think is that you are moving more to a
distributed database architecture and you want to guarentee that the
surrogate key (its not really a primary key, the primary key is part of your
data) is unique across databases.
You can still use IDENTITY but encode a site ID into the schema.
NEWID() is random in its generation so the insert will be random across your
index so, you will cause additional IO because the data will be more spread
across the disk (array) so, you might end up with more locking contention
too.
In a word - don't do it.
Oh, also - its a lot harder to debug and 'see' guids when you are working
with the data under DBA mode ;).
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||2005 will have a new function called:
newsequentialid
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
for 2000
http://www.sqldev.net/xp/xpguid.htm
is an option to overcome the "randomness" of NEWID()
There is a performance hit for using NEWID() in 2000. I won't deny that.
However... .
The big advantage of using GUIDS is that I can
Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
code.
Read my previous post at:
http://groups.google.com/group/micr...be27c58c993dab7
I don't know if there is a super correct answer.
It depends on what you got going on.
Personally, me and my company are making great strides to get the business
logic OUT OF THE Database, and into the business layer.
See
http://www.codeproject.com/gen/desi...sinessLogic.asp
for more info
Most times, I'm going with some kind of GUID usage, but not the NEWID stuff.
If replication is in your plans, then you need to seriously consider
abandoning IDENTITY's.
But you should research and judge for yourself.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Hi Robert,
> Why? The only reason I can think is that you are moving more to a
> distributed database architecture and you want to guarentee that the
> surrogate key (its not really a primary key, the primary key is part of
your
> data) is unique across databases.
> You can still use IDENTITY but encode a site ID into the schema.
> NEWID() is random in its generation so the insert will be random across
your
> index so, you will cause additional IO because the data will be more
spread
> across the disk (array) so, you might end up with more locking contention
> too.
> In a word - don't do it.
> Oh, also - its a lot harder to debug and 'see' guids when you are working

> with the data under DBA mode ;).
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
> etc... would be appreciated.
> Robert
>
>|||My organization is in the midst of trying to replace our UNIQUEIDENTIFIER
clusetered primary keys with IDENTITY fields. Two reasons: 1) making the
clustered index a UNIQUEIDENTIFIER field increases the size of all the
nonclustered indexes; and 2) UNIQUEIDENTIFIER fields generated with the
NEWID() function are not sequential, so your joins will be much less
efficient.
Oh, and I forgot the last one: changing back is a pain!
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
>
You've missed the boat Sloan, the current thinking is to put the business
logic back into the database because its centralised and easier to manage -
plus you get better resource usage through cached execution code etc...
Google Jim Gray and look up some of his thinking on this.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"sloan" <sloan@.ipass.net> wrote in message
news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> 2005 will have a new function called:
> newsequentialid
> http://msdn2.microsoft.com/en-us/library/ms189786.aspx
> for 2000
> http://www.sqldev.net/xp/xpguid.htm
> is an option to overcome the "randomness" of NEWID()
>
> There is a performance hit for using NEWID() in 2000. I won't deny that.
> However... .
> The big advantage of using GUIDS is that I can
> Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
> code.
>
> Read my previous post at:
> http://groups.google.com/group/micr...be27c58c993dab7
>
> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
> See
> http://www.codeproject.com/gen/desi...sinessLogic.asp
> for more info
>
> Most times, I'm going with some kind of GUID usage, but not the NEWID
> stuff.
> If replication is in your plans, then you need to seriously consider
> abandoning IDENTITY's.
> But you should research and judge for yourself.
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> your
> your
> spread
>
> SQL
>|||Why are you considering this? What gains do you expect, or why do you
think you need GUIDs? Aren't Identities working for you?
Have you seen the presentation of Kimberly Tripp on Index Optimization?
(see
http://www.microsoft.com/uk/technet...aspx?videoid=29)
In a fragment of it, she discusses the use of GUIDs as the clustered
index key, and how this use can cause massive fragmentation and (as a
result) abysmal performance.
BTW: there are different opinions about the policy to use an Identity
(or other surrogate key) as the Primary Key for each table. My personal
opinion is, that such a key should never be the first choice. So IMO you
should not have such a policy. One should always try to find a natural
key, and only choose a surrogate key if no useful natural key is found,
or for performance reasons (which means the natural key would still be
an alternate key, enforced with a Unique constraint).
HTH,
Gert-Jan

> rmg66 wrote:
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability,
> managability, etc... would be appreciated.
> Robert
>|||Not trying to pick a fight, Tony, but you're one of the few that I've
seen advocate this. I haven't read any of Jim Gray's stuff, but I'll
take a look at it. It seems odd to me to put business logic back into
the database because of the issues of object-relational impedance,
scalability, and general performance considerations.
I prefer clean seperation, myself; I'm even beginning tto question the
need for stored procs because of the failure to seperate business logic
from data retrieval.
Stu
Tony Rogerson wrote:
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to manage
-
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...|||Well, that's great if you're married to Sql Server.
I love Sql Server, don't get me wrong, its my bread and butter.
But you can't guarantee you'll always be in a Sql Server world.
And I don't think I've "missed the boat".
My DataLayer objects return:
IDataReader's
DataSets (typed and untyped)
XmlDocuments
Scalars
voids (or nothings... as in, just make sure what I called worked)
Because I have a good DataLayer, I can switch out the backend database at
any given moment.
Yeah, there will be some issues, but not as drastic as complicated business
logic in my tsql.
The database is usually the bottleneck of any well designed system.
And the quicker I get in and get out, the better.
I'll take a look at Jim Gray's stuff. (is this the same Jim Gray who does
interviews for espn/nba?)
That's fine to say "There are other options out there, which have BL in the
database"
But "you missed the boat", ... thats a little strong for for advocating
another opinion in favor of what I and alot of others have proposed.
Perhaps the experience of having my company merge with another company, with
diffrent RDBMS systems has influenced me somewhat.
I'll stick with the good DataLayer design for now.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OHgQiOaiGHA.4284@.TK2MSFTNGP05.phx.gbl...
business
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to
manage -
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
that.
DotNet
http://groups.google.com/group/micr...993dab7

business
contention
working
a
managability,
>