July 13, 2009 at 3:36 am
Hello All,
I am designing tables for my .NET project. In my SQL server database, I want an ID column (primary) in almost all tables. Which data type will be best for the primary key column? GUID or integer with identity column set ? I have seen lot of applications using GUID instead of Int. Is there any advantage/disadvantage using GUID?
Please advice.
July 13, 2009 at 3:46 am
Use GUID for a key only if you need the key to be globally unique.
The main disadvantage is the size (in terms of bytes) of a guid. Also the randomness can be an issue , though newsequentialid can overcome this. See this link
edit : Also you cant shout a guid out across the room , 'Can someone take a look at ID , 7763 ?' becomes 'Can someone take a look at ID ,ABCD887....etc ?'. 🙂
July 13, 2009 at 3:49 am
Dave Ballantyne (7/13/2009)
Also the randomness can be an issue , though newsequentialid can overcome this. See this link
That's if the primary key is the clustered index, which it doesn't have to be. There's no real problems with a random guid as the primary key. The problem (rapid fragmentation) comes in when the guid is the clustered index.
Generally I'd say if the primary key is a guid, make the primary key nonclustered and put the clustered index on a more suitable column, if one exists.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 3:54 am
Which one is the common practice now days GUID or INT?
Head that there will be some performance issues while searching through large tables with GUID as primary key.
July 14, 2009 at 12:49 pm
Aneesh (7/13/2009)
Which one is the common practice now days GUID or INT?Head that there will be some performance issues while searching through large tables with GUID as primary key.
Have you thought about using newsequentialid() in your table definition, making it a default value? It stays unique but is also in sequence which would cut down on fragmentation.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 14, 2009 at 4:26 pm
It will an issue for your indexing strategies.
A GUID is 16 bytes wide, an INT is 4 bytes, which essentially means your indexes will be 4 times as big than using an INT.
Since a page can hold 8000 bytes, you can have 500 records of GUID type in each page, but you can have 2000 records of INT type in each page.
Also a GUID is not "globally" unique. It is guaranteed to be unique within same sql server only.
N 56°04'39.16"
E 12°55'05.25"
July 15, 2009 at 1:42 am
Peso (7/14/2009)
Also a GUID is not "globally" unique. It is guaranteed to be unique within same sql server only.
Are you sure?
This is from BOL
The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.
July 15, 2009 at 1:52 am
Yes, I am quite sure.
For a few years ago, I was called in to solve a replication error. There were 3 records out of 2 billion that was not replicated to the central datawarehouse.
The source of the error was that these three records had a GUID that was generated by the client application (4000 simultaneous users).
After some investigations, we found the three "offending" records in the source databases. Editing two of those solved the replication error.
N 56°04'39.16"
E 12°55'05.25"
July 15, 2009 at 2:07 am
Not that i doubt you but, are you 100% sure that that can be the only explanation ? There were no other bugs that could of caused it to happen?
According to wikipedia
While each generated GUID is not guaranteed to be unique, the total number of unique keys (21^28 or 3.4×10^38) is so large that the probability of the same number being generated twice is infinitesimally small. For example, consider the observable universe, which contains about 5×10^22 stars; every star could then have 6.8×10^15 unique GUIDs.
The odds on 3 out of 2 Billion being duplicated are (infinitesimally small cubed) ???
July 15, 2009 at 5:41 am
On the subject of GUIDs, under certain circumstances they are not as unique as you expect. Vulnerable systems are those without network cards, where the OS is installed by image replication software like Ghost.
A GUID is a 256-bit number, where 128 bits are derived from a counter, and the remaining 128 bits are derived from system parameters like MAC address, Windows installation time, etc.
An engineer from InstallShield said he actually saw identical GUIDs generated by laptops - without network card, and the OS installed by Ghost. Now InstallShield recommends having a Network card on the system.
But, if your system has a network card, I assume that the possibilty of identical GUIDs is about the same as the possibility of a bit getting flipped undetected.
July 15, 2009 at 7:33 am
Dave Ballantyne (7/15/2009)
Not that i doubt you but, are you 100% sure that that can be the only explanation ? There were no other bugs that could of caused it to happen?According to wikipedia
While each generated GUID is not guaranteed to be unique, the total number of unique keys (21^28 or 3.4×10^38) is so large that the probability of the same number being generated twice is infinitesimally small. For example, consider the observable universe, which contains about 5×10^22 stars; every star could then have 6.8×10^15 unique GUIDs.
The odds on 3 out of 2 Billion being duplicated are (infinitesimally small cubed) ???
Also from Wikipedia, the original version used MAC addresses from network cards. Apparently you can tell if you see a 1 in the highlighted position: XXXXXXXX-XXXX-1XXX-XXXX-XXXXXXXXXXXX.
This makes it seem possible that in a computer with no network card, there could be a remote possibility of duplication, depending on how the initial value is seeded. The latest version, however, seems to use a PRNG which is much better as a seed.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 15, 2009 at 9:09 am
I would only use guids where they are necessary and never in a clustered index.
As already pointed out GUIDs are not as unique as they should be though you are unlikely to run into run into a duplicate very often.
I also saw a post somewhere with someone who was getting blocking issues bulk inserting into tables using two processes simultaneously. The article mentioned that the locking key is a 6 byte hash and that with compound keys it was possible for different records to generate the same 6 byte hash. The article didn't mention GUIDS but if two GUIDS generate the same 6 byte hash then I suppose the same thing could happen.
July 15, 2009 at 9:14 am
I think David is referring to this
July 15, 2009 at 9:26 am
What's wrong with a bigint identity(1, 1) or any starting point after 1. Bigint is 8 bytes, half that of uniqueidentifier. Granted, it's not as "professional" looking as a GUID but chances are you're not going to fill the 18 quintillion values of Bigint. A regular int is almost 4.3 Billion values, and many databases don't get that many rows, so that's an option as well.
--Edit: Sept. 10, sometimes you just have to fix embarrasing spelling mistakes, LOL.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 15, 2009 at 9:34 am
Gaby A. (7/15/2009)
What's wrong with a bigint identity(1, 1).
Nothing provided you are running on a single server. The instant you go multi-site or employ a sharding strategy all sorts of challenges come to the fore.
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply