I was curious to compare the performance of Integers and GUIDs as primary key values. The common advice is to prefer ints over unique identifiers, since an int takes 4 bytes of space against 16 bytes for a GUID/unique identifier. I assumed that by fitting more rows per page that lookups would be quicker, but I wasn’t sure if other operations would be more efficient as well.
I set up two tables, one with an int primary key using identity to generate the unique values. The second used uniqueidentifier with the newsequentialid function generating the values. Both tables had a clustered primary key. The tests were using SQL Server 2008. For the selects, I cleared the data cache and execution plan cache after each run.
There was a surprise with the tests. The inserts on the GUID tables were actually slightly faster than on the int table. I suspected that the method of generating the PK values may have contributed to the difference, so I tried a second test without a primary key constraint, just inserting the same value 50,000 times so that the generation method wouldn’t affect the results. For this run the times were similar, so it appears the newsequentialid is faster generating values than using identity. The next step would be to test on SQL Server 2012 and compare the time with SEQUENCE generation of integer values against identity.
Retrieving one value took a similar time on each table, as did an primary key index seek to retrieve 10 consecutive values, although the int primary key was slightly faster in each case.
A difference emerged while looking up values from a non-clustered index on an EditDate column. I filled each table with one million records, and ran a query to use a non-clustered index seek to find 1000 consecutive values. The Int table query ran in an average of 101 ms, while the GUID table query ran in an average of 110 ms. Not a big difference, but close to 10%.
A bigger difference emerged when forcing an index seek by using a function on the EditDate column. To scan all one million values took an average of 1674 ms on the Guid table, against an average of 796 ms on the int table.
So as far as inserts and single record retrievals, GUIDs and int appear to be fairly similar. The real difference would be seeks and scans on a non-primary key non-clustered index that required a key lookup.
— Query Results – All times in milliseconds
———————————————————————
— 50,000 Inserts
———————————————————————
GUID: 9828, 11482, 9728
Integer: 10187, 10661, 11151
———————————————————————
— Select 1 PK value from 1 million records
———————————————————————
GUID: 20, 29, 30
Integer: 16, 16, 17
———————————————————————
— Select PK range – 1000 from 1 million records
———————————————————————
GUID: 124, 91, 114
Integer: 106, 121, 98
———————————————————————
— Non-clustered index seek – 1000 records
———————————————————————
Guid: 123, 79, 106, 131
Integer: 90, 95, 90
———————————————————————
— Non-clustered index scan – 1 million records
———————————————————————
Guid: 1672, 1671, 1680
Integer: 767, 836, 786
SQL Rob 