Menu
We have a system that uses UniqueIdentifier as the primary key of each of the tables. It has been brought to our attention that this is a bad idea. I have seen similar post on the subject but I am interested in any MS SQL performance and other potential problems I may encounter due to this decision.
224
Having a guid column is perfectly ok like any varchar column as long as you do not use it as PK part and in general as a key column to join tables. Your database must have its own PK elements, filtering and joining data using them - filtering also by a GUID afterwards is perfectly ok.
Primary keys should never be exposed, even UUIDsA primary key is, by definition unique within its scope. It is, therefore, an obvious thing to use as a customer number, or in a URL to identify a unique page or row. Don't! I would argue that using a PK in any public context is a bad idea.
An INT is certainly much easier to read when debugging, and much smaller. I would, however, use a GUID or similar as a license key for a product. You know it's going to be unique, and you know that it's not going to be sequential.
Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.
There are pros and cons:
This article covers everything.
GUID Pros
GUID Cons
112
I wrote a post about this last week with some code to show you what happens: Some Simple Code To Show The Difference Between Newid And Newsequentialid
Basically if you use newid() instead of Newsequentialid() you get horrible page splits if your PK is a clustered index (which it will be by default)
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
