If you’ve started to use the new Elastic table type, you’re probably familiar with how the
partitionid column is a crucial part of the identity of each record. This new version understands which tables are Elastic and therefore need to include this column when performing
DELETE queries to ensure the correct records are affected.
The CreateMultiple, UpdateMultiple and DeleteMultiple messages are also crucial to getting the best performance out of Elastic tables. They’re also supported for many other table types too! This release uses these new messages for bulk data changes wherever possible.
All these changes are handled automatically behind the scenes so there isn’t anything different you need to do when constructing your queries. Just write your queries as you normally would and SQL 4 CDS will handle the rest for you.
The XML data type in SQL Server provides a lot of flexibility for querying semi-structured data. XML is commonly used for storing things like form layouts and saved queries in Dataverse, so you can use a query like:
SELECT name, (CAST (formxml AS XML)).query('//cell[control/@datafieldname=''telephone1'']/labels/label[@languagecode=1033]') FROM systemform WHERE objecttypecode = 'account'
to find what label is used on different forms for the
There is also a built-in method for base64-decoding data in the XPath query language, so you can get more useful values out of tables like
SELECT TOP 10 CAST (CAST ('' AS XML).value('xs:base64Binary(sql:column("content"))', 'varbinary(max)') AS VARCHAR (MAX)) FROM webresource WHERE webresourcetype = 3
SELECT name, STUFF((SELECT ', ' + fullname FROM contact where parentcustomerid = account.accountid FOR XML PATH ('')), 1, 2, '') FROM account
This is only the first release of XML support so there are probably various queries it does not currently support. Please let me know if you come across examples where it can be improved!
When you use a query which affects many records in one go, SQL 4 CDS will now use the CreateMultiple/UpdateMultiple/DeleteMultiple messages for greater efficiency where possible.
If an error occurs with one record, the query will normally stop immediately. This update includes a new query hint to allow the remaining records to continue to be processed to make it easier to handle large amounts of data where a small number of records may fail:
UPDATE account SET statecode = 1 WHERE name LIKE '%(DO NOT USE)' OPTION (USE HINT ('CONTINUE_ON_ERROR'))
There are various fixes in this release to improve how SQL 4 CDS handles the
audit table. This is built on the same technology as Elastic Tables, but with a few other quirks. This update encodes all those known quirks so you can continue to write queries as you want and SQL 4 CDS will understand how it’s possible to run them.
This release also includes several other bug fixes. Some of the notable ones are:
- In Azure Data Studio, no rows were displayed in the results by default
- Aggregate queries would return incorrect results when being run with multiple threads
- Filters on the
solutiontable combined with outer joins would trigger an error
Thanks to everyone who reported these issues on GitHub!