A question frequently asked during architectural stages of a new Azure-based project: should we choose SQL Azure or Azure Table Storage to store our data?

Answer is typically: YES. Both of these storage technologies compliment rather than compete with each other.  If you are looking to gain maximum scalability, performance, and flexibility while at the same time paying the least amount of money, the trick is to understand the strong points of each technology and utilize both effectively.

Azure Table Services (ATS) is a new storage technology from Microsoft and is specifically designed to handle mega-scalability for applications and websites of Twitter/Facebook/Amazon's capacity.  Storage space is super cheap with ATS.  To offset the low cost and mega-scalability there are a few negative impacts that one must be aware of.  You are not only paying for storing the data, but also for accessing the data that lives in ATS.  There is also only limited support for transactions.  This is key to understand and design around.  ATS also is a new paradigm for developers to grasp.  Lastly, ATS forces your compute nodes to become mini-relational servers.  It simply does not do any of the relational processing we are all used to.  JOINs, GROUP BY's, ORDER BY's all have to be designed around or performed manually. 

I would say that ATS is best suited for large amounts of data that only rarely needs to be accessed or massaged.

On the other hand, SQL Azure is a fast, lighter-weight cloud-based relational storage.  Your developers will know how to code against it right away, because (barring a few small gotchas) coding for it simply like coding for any other SQL database.  On the negative side: SQL Azure is not meant to support huge applications like Facebook, Ebay, Twitter, etc.  Azure, stores your SQL Azure databases along with others in a multi-tenant environment on same servers and thus it has to throttle access should your application become too hot and impacts other databases on the same SQL Azure node.  SQL Azure is also somewhat pricey, at $10/gigabyte/month.  Having said, there is no cost to access the data stored in SQL Azure and there is plenty of CPU power dedicated to perform relational functionality on your data.  

I would recommend that SQL Azure for smaller amounts of frequently accessed data.

A few typical use-cases to illustrate the points:

Data in a banking system that stores customer account information along side a large amount of financial transactions would best divided across SQL Azure and ATS in the following way: Customer account information in SQL Azure, Financial Transactions in ATS.

Content for a large blog site should probably live in SQL Azure until it reaches a certain age and can be archived to ATS.