Hi Ian et al, I reckon most of the replies in the article are spot on regarding price, capacity, speed, transactions, etc (except for the last person who hates Table Storage for reasons I don't think are justifiable). You have to know in your bones if your data is *relational* (normalisable) or not, and if it needs what SQL server give you: joins, optimized queries, transactions, etc. I'm sure anyone worth their hourly rate knows in their bones if you need an RDB or not. If you need an RDB then use Azure SQL.
So when is Table Storage a good choice? I think the choice is narrowed greatly by the fact you must use two string keys per table with no joins. If you can coerce your data to work this way then Tables are great. I use them for log files and lookups. I even have a pair of tables with a fake join property, I can't update them in a transaction, but updates are rare and it doesn't matter in this case. Using the key pair makes me do artificial things at times. For example, my log files have the PartitionKey as the machine name and I make a RowKey like this: yyyyMMddHHmmssfff-nnn The nnn is a cyclic counter suffix to allow up to 1000 inserts in the same time interval. Doing this seems cludgy, but it's what the string keys demand of you. For me, the greatest things about table and blob storage is the ease-of-use. You just add a Nuget package and in a dozen simple lines of managed code you have it working. If I were to use Couch, Mongo, Raven or any other such DB I'd be stuffing around with unfamiliar runtimes, hosts, dependencies, installations and documentation. As a coder, I want things to "just work" without hours of suffering and learning, and the Azure storage APIs are simple and productive. *GK* On 6 March 2016 at 18:45, Ian Thomas <[email protected]> wrote: > I wondered about Azure SQL vs Azure Table Storage pros and cons, and to > lessen my ignorance looked at a few Q&A at Stackoverflow. > > This part of a response (5 years to 2 years old, so the balance may have > changed considerably) is one person’s opinion, but I’d be interested in > Greg Low‘s comments on it: > > > > *When should i use Sql Azure and when should I use table Storage? > <http://stackoverflow.com/questions/4930368/when-should-i-use-sql-azure-and-when-should-i-use-table-storage>* > > this is an excellent question and one of the tougher and harder to reverse > decisions that solution architects have to make when designing for Azure. > > There are mutliple dimensions to consider: On the negative side, SQL Azure > is relatively expensive for gigabyte of storage, does not scale super well > and is limited to 150gigs/database, however, and this is very important, > there are no transaction fees against SQL azure and your developers already > know how to code against it. > > ATS is a different animal all together. Capeable of megascalability, it is > dirt cheap to store, but gets expensive to frequently access. It also > requires significant amount of CPU power from your nodes to manipulate. It > baiscally forces your compute nodes to become mini-db servers as the > delegation of all relational activity is turned over to them. > > So, in my opinion, frequently accessed data that does not need huge > scalability and is not super large in size should be destined for SQL > Azure, otherwise Azure Table Services. > > Your specific example, transactional data from financial transactions is a > perfect place for ATS, while meta information (account profiles, names, > addresses, etc.) Is perfect for SQL azure. > > > > All the other answers to the NULL question that I have seen (for table > storage) have some sort of “clumsy” testing, along the lines that GK has > used. There are several lnks (elsewhere on SO – see the side-panel links to > other questions on null testing) some of which lead to Microsoft guides, > which may be helpful. > > > > Ian Thomas > > Albert Park, Victoria 3206 Australia > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] > On Behalf Of Thomas Koster > Sent: Sunday, 6 March 2016 5:27 PM > To: ozDotNet <[email protected]> > Subject: Re: Azure Table query "not null" > > > > On 4 March 2016 at 18:03, Greg Keogh <[email protected]> wrote: > > > Folks, anyone using Azure Tables Storage in anger? I really like it, > > > simple and effective. > > > > > > What is the query syntax equivalent of SQL "not null", that is, a row > > > has a named property? I have a table with tens of thousands of rows, > > > but only a small percentage contains a property value named > > > ErrorMessage, and I want to select them only. Going ErrorMessage neq > > > "" works but it's too ugly to believe there isn't a better way. > > > > OData has a "null" literal, but I don't know if they have it in Azure > Tables (I have not used it "in anger"). > > > > Have you considered including something in the RowKey so that you can > distinguish these rows from the rest with a range query instead? > > > > -- > > Thomas Koster >
