In running some tests earlier, I ran across results where it seemed inserting records with a GUID as the primary key value was faster than using an Integer identity value. I wanted to follow up and confirm those results.
I create three tables, all one column with a clustered primary key. The first used a GUID (uniqueidentifier) with the default value generated by newsequentialid(). The next two both had integer primary keys, one with the values generated using Identity, and the second generated using a Sequence object.
I tried inserts of varying counts, starting with 100 up to 1,000,000, 13 runs in all. Of the 13, the Guid was fastest 6.5 times (there was one tie with inserting 100 records), the Identity was the fastest 4.5 times. and the Sequence was fastest 2 times.
With the Sequence runs, I didn’t take advantage of the caching feature, so it may be worthwhile to experiment with that setting to see if that helps with performance.
Again, it appears the GUID inserts are faster.
I’ve posted the SQL I used for the tests along with the full results:
Default Inserts – Script and Results