Insert Speed – NewSequentialId vs Identity vs Sequence

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: