Hi Ian, It’s a tough question.
For me, the biggest issue is where the querying really will happen. If 100% of the querying will happen in the application, and every object will just be persisted to the table storage and rehydrated before querying, then a table might suit. That usually leads to very poorly performing applications though, depending upon what you need to achieve. I see this type of app regularly. For example, if I have a stock system and I need to clear the “quantity at last stocktake” value for every stock item, I could rehydrate every single stock item back to some middle tier, change the value, and then write them all back. Might feel clean and pure to some, but will take forever. There’s a big difference when you just say to the DB: “make every value in that column NULL”. It's also worth considering that when you store the attributes of an object in a table store (key value pair store), instead of reading/writing one row, suddenly you might be reading and writing dozens of rows (or table values) to get the same outcome. Potential scale really isn’t an issue for most of these types of DBs now. The current limit is 500GB and that’s going to increase substantially as well. Most of the Azure SQL DB storage is now on SSDs internally and most of the “normal” storage accounts aren’t. There could be a substantial performance difference. Transaction support is another big issue. I was fascinated to see the comment that decided that table storage is a good target for financial transactions. Glad he’s not architecting systems that I work on. Azure table storage has a basic transaction concept but it won’t for example, even deal with you deleting a row and putting another one back in. At least it didn’t last time I checked it out. It had very, very limited concepts of transactions. Table storage is cheaper than DB storage. No argument. But that two are hardly comparable in any way. With table storage, it’s more like you’re building your own clumsy DB. Personally, I’d rather pay for one and use it. It also means you lose the ability to use all the related tooling, much of which can help a great deal. The way I see it is that for most organisations, the data is the most valuable thing they own. It usually outlives generations of applications and just gets slightly morphed into different shapes over time. In most organisations, it’s also accessed by a number of applications, not just one. Designing your data storage for the needs of one version of one app that you need today is a big call, and usually not a very clever one. Would I use table storage for anything? Sure, I can think of scenarios where I might. But it wouldn’t involve financial transactions. And most of the cases where I might have used it, are more related to storing data that doesn’t fit neatly into a relational model. That means that it probably doesn’t work that well for table storage either. In that case, I might well now use a dedicated JSON store like DocumentDB instead. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected] [mailto:[email protected]] On Behalf Of Ian Thomas Sent: Sunday, 6 March 2016 6:45 PM To: 'ozDotNet' <[email protected]> Subject: RE: Azure Table query "not null" 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]> [mailto:[email protected]] On Behalf Of Thomas Koster Sent: Sunday, 6 March 2016 5:27 PM To: ozDotNet <[email protected]<mailto:[email protected]>> Subject: Re: Azure Table query "not null" On 4 March 2016 at 18:03, Greg Keogh <[email protected]<mailto:[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
