SQL Azure
In the positive side of SQL Azure - it is free to access (no cost for storage transactions) and is easy to work with (relational, familiar to developers, can be modeled without worrying about how future queries will work, etc.)
On the negative side, SQL Azure is not mega scalable - even with Federations, each database or federated member still lives in a multi-tenant environment with other databases and they compete with one another on the same server for disk, CPU, RAM, etc.
From pricing perspective, it is more expensive to store data in SQL Azure than in Azure Table Storage, but accessing it is free (no cost for calls made SQL Azure database)
Azure Table Storage (ATS)
On the positive of Azure Storage - it is mega-scalable and can support very large data amounts. Basically, there is no "relational brain" behind ATS like there is a brain behind SQL Server. Because of this, you're not bound by the limitations of having a single brain and you delegate all the relational activities to your own servers/instances of which you can have as many as you want. This gives you ability to mega scale.
On the negative side, ATS is harder to work with, because you have to model your Keys (PartitionKey and RowKey) very particularly in anticipation of your queries. You might even need to duplicate data in multiple ways so that future access to that data can be very streamlined and use PartitionKey/RowKey appropriately.
From pricing perspective, ATS is dirt cheap to store data, but charges for every time you access it (there is a fee per transaction). Price was recently dropped by 10x but still needs to be considered, as a part of your budget
Suggestion
Use ATS when you need mega scale. For example, if you have a social site like Facebook, the most important place to provide scale would be with the News Feed component - as that data is accessed mega frequently and needs to be very quick.
use SQL Azure for hierarchical data storage where relationships are most important and that data is not super-frequently accessed. For example, profile information of your users (email/address/login/preferences/etc).
