“It becomes a technical quiz at times to figure out how to use the pair of string keys effectively. You can finish up putting all of the row data into the keys! One of my live tables has a natural triple compound key, simple in a SQL schema, but in the table I made the PartitionKey one value and MD5 hashed the other two into an 8 byte hexchars RowKey. It feels odd to do this, but it actually works fine.”
Sounds like you might need a database rather than trying to build a lousy one out of table storage ☺ 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 Greg Keogh Sent: Monday, 7 March 2016 10:09 AM To: ozDotNet <[email protected]> Subject: Re: Azure Table query "not null" OData may have a "null", but ATS certainly doesn't have the concept, or a schema, so I'm sympathetic to the fact that IS NOT NULL will not have any kind of direct efficient equivalent. However ... I need some way of finding all the rows with an ErrorMessage property. If there is no way of doing this without a full table scan, then it hints that I'm using the facility inappropriately, in a "relational" way that it doesn't support. My logs RowKey is full of the timestamp, so unfortunately there's no room left to squeeze a flag into it. Which opens up the question of how much information you can squeeze into the Partition and Row keys in a readable and searchable way. It becomes a technical quiz at times to figure out how to use the pair of string keys effectively. You can finish up putting all of the row data into the keys! One of my live tables has a natural triple compound key, simple in a SQL schema, but in the table I made the PartitionKey one value and MD5 hashed the other two into an 8 byte hexchars RowKey. It feels odd to do this, but it actually works fine. GK On 6 March 2016 at 20:51, Greg Low (罗格雷格博士) <[email protected]<mailto:[email protected]>> wrote: If it's NULL, why would you store it at all? Surely the lack of a value is NULL. In SQL Server when they issue XML, they have an option for a way to represent NULL. Normally they just omit the attribute. They normally only do that in case someone is trying to derive the schema from the data. In that case, you want something to let them know there is a column but it currently has no value. For a table storage system (like an EAV system), why store anything? (Unless again, you're somehow deriving the schema from the data that's present). 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> -----Original Message----- From: [email protected]<mailto:[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
