Click here to Skip to main content
Click here to Skip to main content

INT versus GUID on a table's primary key

, 6 May 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
This article tries to show the pros and cons of using INT or GUID types on primary keys.

Introduction

This article shows the pros and cons of using the INT or GUID types on primary keys. Hope it helps you decide which suites your project best.

Using INT on primary keys

Advantages
  • Small amount of storage size (and integer is 4 bytes).
  • Increased readability, practical use in testing, and easy to remember.
  • Chronology of data; if two records are in ascending order, we can deduce that the second record was inserted after the first one.
  • Support for functions that return the last primary key generated (@@IDENTITY, SCOPE_IDENTITY()).
Disadvantages
  • Difficulty in the case of merging tables (the need to make remapping because the primary keys may be duplicated).
  • Hard to work with distributed tables.
  • Primary key in the form of INT/BIGINT is LUID, local unique identifier, which is only used locally in a table.
  • After a large number of operations (insert, delete), the counter for primary key can be reset, bringing the problem of chronology to 1.

Using GUID on primary keys

Advantages
  • Easy to merge tables.
  • Easy to work with distributed tables.
  • The primary key is uniquely identified in the entire system (the number of machines or tables doesn't matter).
  • Don't have problems when inserting a large number of operations.
Disadvantages
  • Bigger storage size (16 bytes - four times the size of an integer).
  • Don't have the chronology assumption.
  • Hard to remember and use in testing.
  • Don't benefit from a mechanism to obtain the last generated primary key (MS SQL Server).

My decision

I had a hard time deciding which solution suites best for my project, but I finally used GUIDs because of the advantages mentioned. But this happened only once, the rest of my projects have databases with INT primary keys. Hope someone finds this useful. Special thanks to Iancu Caputa for helping with this article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Liviu Holhos
Software Developer
Romania Romania
Web developer, designer and basketball enthusiast.
Follow on   Twitter

Comments and Discussions

 
QuestionGreat article, but missing one important thing. PinmemberDaniel Carnielli21-Nov-12 5:36 
Generalok but PinmemberDonsw14-Jun-09 16:30 
QuestionComposite PK on two integer fields? Pinmemberpashaper11-Nov-08 23:22 
GeneralGUIDs fragmentation PinmemberGeyzerskiy Dmitriy8-May-08 2:45 
When comparing INT with GUID I would mention another GUID disadvantage.
If you use NEWID() T-SQL function to generate unique identifiers you should be aware of the fragmentation problem. Usually tables using GUIDs are highly fragmented due to the nonsequential nature of GUIDs. If you use NEWSEQUENTIALID() T-SQL function for generating sequential GUIDs you should be concerned with the privacy, since given any GUID all the others may be guessed without any problem.
The following T-SQL code shows high fragmentation level for table populated with GUIDs:
 

IF object_id('TBL_GUIDS') is not null
DROP TABLE TBL_GUIDS;
 
CREATE TABLE TBL_GUIDS(guid_col uniqueidentifier primary key default (NEWID()),
updated_date datetime default (GETDATE()));
 
declare @counter int
SET @counter = 0
WHILE @counter < 10000
BEGIN
INSERT INTO TBL_GUIDS DEFAULT VALUES;
SET @counter = @counter + 1;
END
 
SELECT *
FROM sys.dm_db_index_physical_stats
(db_id(), object_id('TBL_GUIDS'), NULL, NULL, NULL);
 

 
If you run the same code, but instead of using GUID you use BIGINT and IDENTITY, you will experience low fragmentation.
It is not a secret that fragmentation has very severe impact on SQL Server performance.
So if performance is a concern, don't use GUIDs.
GeneralRe: GUIDs fragmentation [modified] PinmemberLiviu Holhos8-May-08 21:42 
GeneralRe: GUIDs fragmentation PinmemberMike Lang13-May-08 3:54 
GeneralRe: GUIDs fragmentation PinmemberLiviu Holhos13-May-08 21:05 
GeneralRe: GUIDs fragmentation PinmemberGeyzerskiy Dmitriy13-May-08 23:48 
QuestionWhat about Performance PinmemberDoug K. Wilson7-May-08 18:07 
AnswerRe: What about Performance PinmemberLiviu Holhos7-May-08 20:46 
GeneralRe: What about Performance Pinmemberastanton197820-May-08 2:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 7 May 2008
Article Copyright 2008 by Liviu Holhos
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid