Unique identifier (guid) as primary key in database design

by

A 128-bit GUID (uniqueidentifier) key is of course 4x larger than a 32-bit int key. However, there are a few key advantages:

  • No “IDENTITY INSERT” issue when merging content
  • If you use a COMB value instead of NEWSEQUENTIALID(), you get a “free” INSERT timestamp. You can even SELECT from the primary key based on a date/time range if you want with a few fancy CAST() calls.
  • They are globally unique, which turns out to be pretty handy now and then.
  • Since there’s no need to track high-water marks, your BL layer can assign the value rather than SQL Server, thus eliminating the step of SELECT scope_identity() to get the primary key after an insert.
  • If it’s even remotely possible that you could have more than 2 billion records, you’ll need to use bigint (64 bits) instead of int. Once you do that, uniqueidentifier is only twice as big as a bigint.
  • Using GUIDs makes it safer to expose keys in URLs, etc. without exposing yourself to “guess-the-ID” attacks.
  • Between how SQL Server loads pages from disk and how processors are now mostly 64-bit, just because a number is 128 bits instead of 32 doesn’t mean it takes 4x longer to compare. The last test I saw showed that GUIDs are nearly as fast.
  • Index size depends on how many columns are included. Even though the GUIDs themselves are larger, the extra 8 or 12 bytes may be insignificant compared to the other columns in the index.

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.

More Related Contents:

  1. How can you represent inheritance in a database?
  2. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  3. Use float or decimal for accounting application dollar amount?
  4. How to create materialized views in SQL Server?
  5. When/Why to use Cascading in SQL Server?
  6. What columns generally make good indexes?
  7. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  8. SQL Server: the maximum number of rows in table [closed]
  9. SQL Unique constraint across multiple tables
  10. Best way to store time (hh:mm) in a database
  11. Database design: Calculating the Account Balance
  12. How to store directory / hierarchy / tree structure in the database?
  13. Date range overlapping check constraint
  14. “Prevent saving changes that require the table to be re-created” negative effects
  15. How to choose the clustered index in SQL Server?
  16. How to wait for 2 seconds?
  17. How do you open an SDF file (SQL Server Compact Edition)? [closed]
  18. Can I have a foreign key referencing a column in a view in SQL Server?
  19. How do you add a NOT NULL Column to a large table in SQL Server?
  20. 7645 Null or empty full-text predicate
  21. SQL Server backup/restore vs. detach/attach
  22. How to get a list of all the MS SQL Server instances on the local machine?
  23. Find broken objects in SQL Server
  24. mysqldump equivalent for SQL Server
  25. What is Multi Dimension OLAP CUBE and give example cube with more than 3 dimensions
  26. Bootstrapping SQL Express from WiX?
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. EAV over SQL Server
  29. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  30. The Best Way to shred XML data into SQL Server database columns

Leave a Comment