Our data resides in a SQL Server 2008 database, there will be a lot queries and joinings between tables. We have this argument inside the team, some are arguing use of integer identity is better for performance, some are arguing use of guid (unique identifier).
Does the performance really suffer that badly using a GUID as a primary key?
A primary key, also called a primary keyword, is a column in a relational database table that's distinctive for each record. It's a unique identifier, such as a driver's license number, telephone number with area code or vehicle identification number (VIN). A relational database must have only one primary key.
int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case using sql server guid would be better than and int and why? From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.
The globally unique identifier (GUID) data type in SQL Server is represented by the uniqueidentifier data type, which stores a 16-byte binary value. A GUID is a binary number, and its main use is as an identifier that must be unique in a network that has many computers at many sites.
A GUID is a "Globally Unique IDentifier". You use it anywhere that you need an identifier that guaranteed to be different than every other. GUIDs are generally used when you will be defining an ID that must be different from an ID that someone else (outside of your control) will be defining.
A 128-bit GUID (uniqueidentifier
) key is of course 4x larger than a 32-bit int
key. However, there are a few key advantages:
SELECT
from the primary key based on a date/time range if you want with a few fancy CAST()
calls.SELECT scope_identity()
to get the primary key after an insert.bigint
(64 bits) instead of int
. Once you do that, uniqueidentifier
is only twice as big as a bigint
.In the end, squeezing out some small performance advantage by using integers may not be worth losing the advantages of a GUID. Test it empirically and decide for yourself.
Personally, I still use both, depending on the situation, but the deciding factor has never really come down to performance in my case.
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