SQL 4 CDS v7.3 Released

This update includes support for the new Elastic Tables feature, XML data type and more.

Elastic Table Support

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 UPDATE and 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.

XML Data Type

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 telephone1 attribute.

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 webresource:

SELECT TOP 10 CAST (CAST ('' AS XML).value('xs:base64Binary(sql:column("content"))', 'varbinary(max)') AS VARCHAR (MAX))
FROM   webresource
WHERE  webresourcetype = 3

You can also use the FOR XML clause to generate XML data from other queries. A common pattern is to combine this with the newly-supported STUFF method to create comma-separated lists:

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!

Bulk INSERT/UPDATE/DELETE queries

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'))

Audit Queries

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.

Bug Fixes

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 solution table combined with outer joins would trigger an error

Thanks to everyone who reported these issues on GitHub!