GUIDs VS. IDs on Azure SQL Database

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 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 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.

Ingesting Massive Amounts of Relational Data With Windows Azure SQL Database (70+ Million records/day)

In a previous post I discussed using Table Valued Parameters and Stored Procedures to insert large amounts of data into Windows Azure SQL Database with reasonable  throttling by the SQL Database. Not long after that post, I rapidly came to the conclusion that it was a great solution for reasonable amounts of data. Then I wrote about using SqlBulkCopy to overcome the limitations imposed by the stored procedure approach and provided the ReliableBulkWriter.

This article will show how to organize a  database to enable the insertion of large amounts of  relational data. To illustrate the changes that were required I will be using the following model.

 

Optimizing MERGE Performance in Azure SQL Database

When merging large datasets in Azure SQL Database its imperative to optimize our queries. Failure to do so will most likely result in 40552 : The session has been terminated because of excessive transaction log space usage.

40552 : The session has been terminated because of excessive transaction log space usage.

If you are not familiar with the following Windows Azure SQL Database error, you will rapidly come to understand that SQL Database is very good at protecting itself  from abuse or anything it considers abuse. I regularly come across this specific error when I ask too much of SQL in a single transaction.

40552 : The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.