Can Azure table storage be compared to a relational database?

In this article, I am going to discuss one of the features of Microsoft’s Azure Cloud Services called Azure Table Storage and try to compare it with more familiar Relational Database systems such as SQL Server.

Azure Table Storage is, in its simplest form, a way of storing rows of data in Microsoft’s Azure Cloud Environment. Azure table storage consists of any number of Tables, Tables consist of any number of Entities and Entities consist of up to 252 Properties. You can think of Entities as being analogous to Rows and Properties as being the fields within rows. Properties consist of a Name for the property and a value in one of the standard .NET Framework data types such as string, integer, Boolean, datetime etc.

Each Entity has 3 common Properties called PartitionKey, RowKey and TimeStamp. The PartitionKey property and RowKey property are string values that uniquely identify the row within the table, acting like a primary key. The TimeStamp maintains when the row was last updated to allow for some basic optimistic concurrency, i.e make it harder for 2 people to update a row at the same time.

So far, this seems just like a Relational Database. However, one thing a Relational Database Table has that an Azure Table Storage Table doesn’t have is a schema. The Tables in Azure Table Storage have no structure defined in them at all. In fact, you could almost think of them as merely being folders where the Entities are stored.
The entities themselves are responsible for handling their Properties. What this means is that you could have a Table with Entities with entirely different Properties. This becomes useful when you want to store different types of data in the same table, or if you expect to changing your data structures often.

For example, there might be a table called Products that has an entity for a DVD product that has Properties for Age Classification, Running Time and Region. The same table could also hold a Product entity for a pair of shoes that has Properties for Material, Size, Colour and style.
In a conventional Relational Database, this would require a number of tables that are linked together via Foreign Keys as all rows within a table have to follow the same schema.
The drawback of Azure Table Storage is that the lack of relationships between Tables means that it becomes difficult to store data that is naturally relational. For example, an order management system that stores details on orders, products, customers, suppliers and shipping agents would be difficult to implement in Azure Table Storage.

To simulate Relational items in Azure Tables, you generally have to use some form of unique identifier such as a Globally Unique Identifier (GUID). You would then use this GUID as the RowKey property for Entity and use this GUID as the value of a Property that references this. The problem with this is that there is no referential integrity to prevent rows being deleted.
Another drawback of Azure Table Storage is the overhead in storage space. Having to store the Property Name against each Property in an Entity obviously uses a lot more space that storing the schema against the table in a conventional Relational Database system.

There is also massive differences in the queries that be run on an Azure Table versus a SQL Server table. SQL Server allows a vast range of incredibly complicated queries to be run because the data has a known schema. Azure Tables on the other hand are restricted to simple queries that look for Entities with Properties that satisfy a condition. There is no concept of a SQL JOIN in Azure Table Storage.

For some situations, an important consideration could be cost. As of February 2012, the pricing for Azure Table storage is $0.15 to store a gigabyte of data for a month. There is also a request charge of $0.01 for every 10,000 requests. If you compare this to an Azure SQL Database, this costs $9.99 for a month’s use of a 1 gigabyte database, however there are no request charges.
These cost breakdowns show that SQL Azure would be suited to storing small amounts of frequently accessed data, whereas Azure Table Storage would be suited to long term storage of large data. In fact a real world system would likely make use of both technologies, using the best parts of each for what they are most suited for.

In conclusion, Azure Table Storage offers virtually unlimited scale in terms of load and storage amounts. This is achieved by removing much of the functionality of a Relational Database system. By removing much of this functionality to allow for scalability, it makes itself unsuitable for some situations where a standard Relational Database suits. I will go into more details on this in a future blog.

by Austin Waddecar on 29/09/2012

Let's talk

We're here to help. Send us a message and we'll get back to you as soon as possible.

Get in touch

Guides and Resources

View Resource Library