GUIDs VS. IDs on Windows Azure SQL Database

January 13, 2014 — 3 Comments

shutterstock_111131882 This is an age old war and this is my take on it.

GUIDs are awesome, especially when you need to synchronize data between data sources. IDs are great for speed. To be honest, it’s a memory thing and you need all the speed you can get when you build stuff on Windows Azure.

Since SQL Database like SQL Server uses 8 Kilobyte pages to store data, using IDs will allow you to store more data and optimize for operations like JOINs and MERGEs.

That being said, when I am asked whether to use a GUID or an ID as a primary key for tables stored in Windows Azure SQL Database, I usually give the following advice. Use IDs for lookup data and use GUIDs for data that is susceptible to synchronization and to public use.

Consequently, if you are using GUIDs as primary keys, I strongly recommend creating clustered indexes on non-primary key columns. This will allow you to further optimize data retrieval.

3 responses to GUIDs VS. IDs on Windows Azure SQL Database

  1. 

    Using a GUID as the primary key column does horrible things to Windows Azure SQL Database, because the PK has to have a clustered index, and using non-sequential values such as GUIDs in clustered indexes uses huge amounts of space and slows down inserts.

    It’s better to use an INT IDENTITY column for the primary key, and a UNIQUEIDENTIFIER in a secondary column with a non-clustered index if you want a GUID for public use.

    The overhead of fetching back the SCOPE_IDENTITY value is less than the overhead of inserting into a table with a clustered index on a GUID.

    Like

    • 

      agreed, but you can create your clustered key over non-primary key columns. If you look at the last paragraph, you will find a link to a post about doing just that.

      this does in fact allow you to have GUIDs are primary keys without fragmenting your index. It also allows you to change your clustered index if the need ever rises from your table’s use.

      Like

Trackbacks and Pingbacks:

  1. Reading Notes 2014-01-20 | Matricis - January 20, 2014

    […] GUIDs VS. IDs on Windows Azure SQL Database – Nice post, where the author gives his answer to a timeless question. […]

    Like

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