GregL, thanks for the broad-ranging comments. It’s interesting that many of the discussions (Stackoverflow and elsewhere) get stuck on “to null or not to null”, or a black-and-white discussion about SQL vs NoSQL databases (with little differentiation between flavours of either). Some of them (again, interestingly) quote Date vs Codd – they’re probably the new graduates.
There are lots of discussions about Null. A persuasive argument against is that the term means different things (or means nothing) to different languages used to query the data. I have had a quick read of the (Microsoft) online documentation about designing for Azure, including Azure Table Databases, and on the whole it seems sensible (after just an hour’s eyeballing). Your pointing out the physical storage for Azure SQL being SSDs is certainly a determining factor, and its cost would be reflected in the cost differentials for the various Azure SQL services I guess. Not related, but have you heard that Facebook’s “cold storage” on Bluray is to be developed by Panasonic into a commercial system? Currently 100Gb and 300Gb disks (Facebook), with 500Gb and 1Tb the aim for Panasonic’s commercial system. 50% cheaper, 80% more energy-efficient than hard disks. Regarding ATS for transactional data, I thought it was a facile statement from that contributor to the SO question I linked to (maybe reflecting his/her experience), but in that same thread/question the final/latest contribution (Ken Smith) was rather fiercely condemnatory of Microsoft for not making ATS a type of SQL database. That’s what the internet tosses up. I would have thought the “lack of progress” he decries is because ATS is designed for a purpose, and not beyond. Ian Thomas Albert Park, Victoria 3206 Australia From: [email protected] [mailto:[email protected]] On Behalf Of Greg Low (??????) Sent: Sunday, 6 March 2016 10:19 PM To: ozDotNet <[email protected]> Subject: RE: Azure Table query "not null" 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: <http://www.sqldownunder.com/> www.sqldownunder.com From: [email protected] <mailto:[email protected]> [mailto:[email protected]] On Behalf Of Ian Thomas Sent: Sunday, 6 March 2016 6:45 PM To: 'ozDotNet' <[email protected] <mailto:[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: <http://stackoverflow.com/questions/4930368/when-should-i-use-sql-azure-and-when-should-i-use-table-storage> 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 < <mailto:[email protected]> [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
