`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
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/
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.
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.
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.
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.
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).
