question

Gopal avatar image
Gopal asked

GUID performance and usage

`Hi

I would like to create GUID as primary key of my tables BRANCHMAST AND DIVISIONMAST.

Is it any performace issue of creating GUID. What it is the advantage of using GUID. Some of the forum says that if huge table, GUID is consum more memory it seems.

 

Please suggest.

 

Gopal

best-practiceguid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

short answer: Don't do it!

Not only are GUIDs large (16 bytes compared to 4 for INT or 8 for BIGINT), they are usually random. It is the randomness that makes them so bad as primary keys (assuming the usual case that the primary key is the clustered index). Since SQL Server 2005, you can use the function NEWSEQUENTIALID() to get ordered GUIDs.

There are numerous articles on this topic, but here are 2 I found from well-known sources.

Brian Moran in 2005:
http://www.sqlmag.com/article/performance/performance-effects-of-using-guids-as-primary-keys.aspx

Kimberly Tripp:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx

I found another relevant article on a website you may recognize:
http://www.sqlservercentral.com/articles/Miscellaneous/alookatguids/2497/

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tend to agree here. I would also stay away from NewSequentialID() since it has been shown to not always provide sequential values. There are methods from the application layer that can provide the sequence needed with a GUID, and not impact performance like a GUID is prone to do. However, for the PK, I would go with the Identity, natural key or something else before a GUID. Only use the GUID if it makes absolute sense.
2 Likes 2 ·
+1 Completely agree. It was the use of GUIDs as a clustered index key that brought down the Windows 7 download site.
1 Like 1 ·
+1 good links :)
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

I agree with everything Tom said, but it is worth expanding in a way that will not fit in a comment.

First, I would very rarely put a clustered index on a GUID. I might consider using a GUID as a primary key for logical and design reasons (with a unique non-clustered index), but only in very specific circumstances and when I did that I would try very hard to find something else to put the clustered index on.

Next, think very carefully before using either the GUID or the NEWSEQUENTIALID() function at all. At the risk of touching off a holy war, I prefer using natural keys and will only add a surrogate key if the data has no natural key. Even if using a surrogate key, GUID's are still a poor option and a simple identity column will often serve well. When using GUIDs at all, NEWSEQUENTIALID is almost always better for the reasons Tom listed. Particularly if you really had to put the clustered index on the GUID, I would strongly prefer the NewSequentialId then. The only circumstance I would use a truly random GUID is if I needed the order to be deliberately random for some reason.

The one circumstance that comes to mind where GUID's (using NEWSEQUENTIALID) are frequently useful is in merge replication. That is not the only one, but it is probably the only one that comes up frequently.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - well explained. One more circumstance that comes to mind is disconnected relational data - i.e. you have a client that wants to create relational data on their disconnected workstation / laptop, and then upload it to the server later - GUIDs can be handy so you don't have to have a 'remap the relationships' phase...
3 Likes 3 ·
Good point, Matt. Related to your comment (and as Timothy said), SQL Server merge replication uses GUIDs. In fact, it will add a uniqueidentifier column if one does not already exist.
0 Likes 0 ·
Ian Roke avatar image
Ian Roke answered

I hate, hate, HATE GUID's as a primary key or any other sort of key. You are looking at trillions of lines of data before you fill that amount of space with an integer...

However...

Recently there was a really interesting article by Seth Delconte on SSC on Randomizing Result Sets with NEWID which I found very interesting as a "good" way of using GUID's so check that out.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I don't disagree with what anyone has said. Everything is 100% accurate.

And yet...

As a construct GUIDs are extremely useful for developers. There are a number of reasons why you might be working on a design that can take serious advantage of them. Instead of simply dismiss them because they're too big (they are), too random (which leads to serious index fragmentation) and too difficult to work with (quick, what's that GUID we need for customer x?), we need to recognize that they do have utility and find ways to work with them. NEWSEQUENTIALID is not perfect, but it goes a very long way to solving the random nature of GUIDs which radically helps with the fragmentation issue. While 16 bytes is 2 to 4 times as large as INT values, so? It means indexes on the GUIDs have 2 to 4 times as many pages, but that can be dealt with and you do have the possibility of using index compression now. You can monkey with the fill factor to try to keep the fragmentation of your indexes down (although you'll be further increasing the number of pages in the index). There are trade-offs. Go into any situation that might involve GUIDs with eyes open to the issues, but listen to the needs of the business and determine if possibly, this less than perfect solution from a data storage & retrieval standpoint, is functionally more perfect than others might be.

While this baby is ugly, I would not suggest throwing it out with the bathwater.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As usual, you have provided some good food for thought. It led me to add an additional answer about a use for GUIDs in an application I recently started supporting. It's too much to explain in a comment, so I wrote a 2nd answer.
0 Likes 0 ·
Excellent addition and fully agreed. There are times they come in very handy, such as merge replication and the situation Matt described. Still, I would use them cautiously and almost as a tool of last resort. When nothing else will do, it is very good to have them as an option, but only when it is completely clear there are no other better options.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

This is in response to Grant's answer, but it's too long for a comment.

I agree, Grant. If GUIDs had nothing to offer, why would SQL Server have a uniqueidentifier data type? I just started supporting an application because the original developer left the company. He used GUIDs as the primary key for one table. When I asked him why, he gave me 2 reasons.

  1. He is using this value in a URL QueryString and wanted values to be unpredictable and unique.
  2. (his answer) You must use a GUID for multi-server communication.

In response, I told him I agreed with #1 but then explained the index issues related to GUIDs and alternatives for #2. One of the changes I'll be making in the near future is to add a new surrogate primary key (and clustered index) that is an int or bigint identity column. The GUID column will remain (and be the key for a nonclustered index).

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah, sounds reasonable. Don't get me wrong, I find the things as problematic as anyone else does, but it's awfully easy for us, as DBA's to come as too negative on something just because it doesn't fit neatly within our preconcieved, albeit accurate, notions.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.