October 11, 2010 at 9:32 pm
Comments posted to this topic are about the item Are There That Many GUIDs?
October 12, 2010 at 1:01 am
If you use merge replication GUID's are a perfect primary key .
October 12, 2010 at 2:04 am
Guids are possibly the worst choice to use for a primary key if you intend to index on them. They cause up to 90% fragmentation on the index. The incremental guid also fragments but by far less. There are lots of articles out there on why guids should not be used for primary keys when you intend to use it as an indexable column. I prefer to keep my indexes narrow and with as little possibility to fragment as possible.
Theres a good article in SQLMag which describes why this happens, we also had some good discussions at the last SQLbits in York with Maciej Pilecki from the SQL Server Team at Microsoft, who stated they shouldnt be used fr this very reason.
October 12, 2010 at 2:21 am
Steve - I use them and most of the time you don't need to remember them...
By the way, what is 'One Orange Chip' ?
October 12, 2010 at 3:32 am
I really don't see the point in them other than for replication amongst multiple servers.
They take up a lot of disk space (relative to an integer), don't index very well, and I've never needed to identify which table a record is from based on the ID alone so I dont care that I have multiple records with ID 1 in seperate tables across by database.
I'd strongly recommend against not using them unless you have to.
October 12, 2010 at 4:45 am
Not sure I get the sequential GUIDs comment. Do all the GUIDs in the different tables start with 00000.....01? Then they wouldn't be unique across the database. You could have a different starting point for each table, but that's a pain.
Having worked extensively with both GUID based and non-GUID based keys, I find the first much harder to deal with--the keys can't be learned, they are out of order, they take up a lot of space. The only use I can see is for a replicated system, and there are even ways around that that should be considered.
October 12, 2010 at 5:37 am
I think there's a bit too much fear around GUIDs. In my own experiences, as long as they're not the clustered key, even the sequential ones, you generally won't have any more issues with them than you will an equally long data type. They're a bit fat, but are highly selective, so it all seems to work out well enough on most systems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2010 at 5:41 am
I think there's a bit too much fear around GUIDs.
Fear isn't the issue. In situations for which there is a simpler solution, they are a royal pain.
October 12, 2010 at 6:01 am
RonKyle (10/12/2010)
I think there's a bit too much fear around GUIDs.
Fear isn't the issue. In situations for which there is a simpler solution, they are a royal pain.
'
Oh absolutely. They should be used where appropriate, like any other data type. No question. But I think there's quite a few people who won't use them, regardless of appropriateness.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2010 at 6:07 am
It's been my experience that GUID's are frequently implemented incorrectly, for example, using the NEWID function instead of the NEWSEQUENTIALID function. This typically causes massive amounts of page splitting and fragmentation. As was pointed out, they also offer little value as a clustered key. The only real value I see is using them in merge or updatable transactional replication. Frankly, I can see no benefit in having a 16-byte random value that I have no control over. I'd much rather use a 4 or 8 byte value that I can control in terms of seed and increment. There's also something to be said for the ability to assign ranges of values. I have used this capability many times when integrating data from diverse data sources into one table, i.e. zero to one billion represents one source, one to two billion another source etc. I can tell, as can the users, at a glance which source the data is from. Can't do that with GUID's.
BOL says it pretty clearly:
"Consider using the IDENTITY property when global uniqueness is not required, or when having a serially incrementing key is preferred."
Also from BOL:
"The uniqueidentifier data type has the following disadvantages:
The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
The values are random and cannot accept any patterns that may make them more meaningful to users.
There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key."
"Beliefs" get in the way of learning.
October 12, 2010 at 6:43 am
GUIDs have the advantage that you can generate them before (or without) doing the insert on the table, which can be quite useful, especially in parent/child screens
Yes they are bigger than int, but this argument is a lot less relevant today than it was 25 years ago when a 100 Mg hard drive was considered science-fiction
fyi, Microsoft CRM (MS Dynamics) uses GUIDs as clustered primary keys for all tables, at least the way it has been implemted here
October 12, 2010 at 6:43 am
I've used both extensively, and have found myself preferring non-GUIDs for the last year or so, simply because of ease of use. It's just plain much easier to deal with an INT than a long freaky string like that, much less the indexing issues of not being able to have the clustering index be on my PK (and size of index). There are mid-tier advantages to using GUIDs, as previously stated with the client being able to create the key and know what it is before calling the insert/save procedure (instead of getting the value back in an output var or such).
If you have a table that may contain unique IDs from several other tables then a GUID works great. For example, we have a "Tags" table whereby several different entities save tags in one table. Those entities/tables have INTs as PKs, but also have a unique GUID in the table that is indexed (nonclustered), and this is the key that we save in the Tags table. For me, that is the one case where a GUID rules, but all in all, I recommend an INT PK for most things.
October 12, 2010 at 6:48 am
paul s-306273 (10/12/2010)
Steve - I use them and most of the time you don't need to remember them...By the way, what is 'One Orange Chip' ?
I've had to try to enter them in queries when debugging things or looking for data. Cut and paste works, but it's a pain at times. Much easier to scan a list of integers or natural keys and type those values, for me.
One Orange Chip is the color of the 25k chip at the Bellagio in Las Vegas, or at least it was when I was there.
October 12, 2010 at 6:50 am
André Lozeau (10/12/2010)
fyi, Microsoft CRM (MS Dynamics) uses GUIDs as clustered primary keys for all tables, at least the way it has been implemted here
I'm not sure that's a good thing! 😉
October 12, 2010 at 7:01 am
André Lozeau (10/12/2010)
GUIDs have the advantage that you can generate them before (or without) doing the insert on the table, which can be quite useful, especially in parent/child screensYes they are bigger than int, but this argument is a lot less relevant today than it was 25 years ago when a 100 Mg hard drive was considered science-fiction
fyi, Microsoft CRM (MS Dynamics) uses GUIDs as clustered primary keys for all tables, at least the way it has been implemted here
That may be. It just goes to show you that even Microsoft sometimes allows the developers to dictate data architecture rather than the data guys. (It's been my experience that object oriented developers LOVE guids for some reason.)
"Beliefs" get in the way of learning.
Viewing 15 posts - 1 through 15 (of 169 total)
You must be logged in to reply to this topic. Login to reply