My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a new company using guid and it seems alot slower but maybe its my imagination. Amazingly this is the first place I've seen it used for the uniqueness of a row. Any help or thoughts you have as to why use one versus the other?
1
-
Please tag this question with your server and version! I'm marking this as a duplicate as it already has an answer - see here and links within!– VéraceJan 16, 2020 at 17:28
Add a comment
|
2 Answers
There are certain situations where a GUID might be needed, but in general I'd advise against it. The main reason is that GUIDs are, by their nature, random. So using them as an identity column essentially means you're storing your rows randomly. With a typical incremental primary key, the data is sequential. Table scans are usually more efficient, etc.
In the end, there are tradeoffs with either situation. Brent Ozar has an older article on the topic: https://www.brentozar.com/archive/2014/08/generating-identities/
I have seen GUID's used where there is a fear of having duplicate primary keys. This is usually seen where you have poor integration of development data into QA or SYSTEST environments. At the other end of the spectrum one Opentext application I supported only had one integer sequence which was used by every table. Voila..no duplicates!
It's also used if you bringing in data from another source that uses GUID's as a primary key such as MongoDb.
My understanding is that using GUID's as a primary key is more costly to search than an integer index.
Jeff Atwood has a blog post here with more details. Any decision like this should have a use case. Something you can't do or can do better by using or not using GUID's
