SQL 4 CDS v7 Released

Or, the Power Platform Conference 2022 Edition! I’m finishing this post from the conference now – if you’d like to say hello while I’m here please get in touch!

Execute Messages

The big new feature in this release is being able to execute more Dataverse messages in your queries as well as just retrieving data from the normal tables. This opens up a lot of new possibilities, but one great example is working with audit data.

Querying the audit history table has been a common request in forums over the years, and there’s never been a great answer. The data is stored in the audit table, but not in an easy-to-use format. In recent Dataverse updates the important columns of this data aren’t directly accessible with a query any more either.

The supported way to access this data is with the RetrieveRecordChangeHistory function, but this isn’t easily accessible for non-developers. A number of tools have been created to make this easier, such as the Audit History Extractor tool in XrmToolBox. With this latest version of SQL 4 CDS you can now also get to this data with SQL:

SELECT contact.contactid,
       audit.createdon,
       JSON_VALUE(audit.oldvalues, '$.firstname'),
       JSON_VALUE(audit.newvalues, '$.firstname')
FROM   contact CROSS APPLY (SELECT *
                            FROM   RetrieveRecordChangeHistory(contact.contactid)) AS audit
WHERE  contact.firstname = 'Mark'
       AND JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

This will find all contacts with a first name of “Mark” and all the times the first name has changed in the past.

Table Valued Functions

Dataverse messages are exposed in SQL 4 CDS as both Table Valued Functions (TVF) and Stored Procedures. The example above uses the TVF syntax – the Dataverse message name (“RetrieveRecordChangeHistory”), followed by a list of parameter values in brackets.

The RetrieveRecordChangeHistory request takes two parameters – the “Target” (the ID of the record to get the audit history for), and paging information to get multiple pages of the audit history data. SQL 4 CDS automatically handles the paging for you, so only the first parameter is required. In this example I’m taking the contactid column from the contact table and using it as the parameter. The OUTER APPLY syntax ensures the RetrieveRecordChangeHistory function is called for each contact in the list.

If you wanted to get the history for a single contact based on its ID, you could do this in two ways. One would be to simply change the WHERE clause in the example above:

SELECT contact.contactid,
       audit.createdon,
       JSON_VALUE(audit.oldvalues, '$.firstname'),
       JSON_VALUE(audit.newvalues, '$.firstname')
FROM   contact CROSS APPLY (SELECT *
                            FROM   RetrieveRecordChangeHistory(contact.contactid)) AS audit
WHERE  contact.contactid = '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'
       AND JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

A simpler query would be to invoke the RetrieveRecordChangeHistory function directly without the CROSS APPLY:

SELECT createdon,
       JSON_VALUE(oldvalues, '$.firstname'),
       JSON_VALUE(newvalues, '$.firstname')
FROM   RetrieveRecordChangeHistory(CREATELOOKUP('contact', '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'))
WHERE  JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

In this example I’ve had to use a custom function CREATELOOKUP to combine the entity name “contact” with the ID of the record to get the audit history for.

Stored Procedures

The same Dataverse messages can also be invoked using stored procedure syntax. This lets you capture the output values in different ways, but can’t be used in combination with the CROSS APPLY syntax to execute it automatically for multiple records.

DECLARE @contactid AS [MarkMpn.Sql4Cds.Engine.SqlEntityReference] = CREATELOOKUP('contact', '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12');
EXECUTE RetrieveRecordChangeHistory @Target = @contactid;

Supported Messages

Not all Dataverse messages are supported. To be able to be used by SQL 4 CDS, a message must:

1. have either no input parameters, or only simple scalar values (strings, numbers etc.) Messages that use complex input parameter types are not supported
2. have either:
   1. no output values (message will be callable as a stored procedure only, not a table valued function), OR
   2. have only simple scalar type output values (strings, numbers etc.) When using the TVF syntax a single row will be produced with these output values. OR
   3. have only a single Entity or EntityCollection type output value of a known entity type.

Custom Actions and Custom APIs can be called provided they satisfy these requirements. It is not possible to define the entity type of an EntityCollection result from a custom action/API and so these messages will not be accessible.

The one exception to these rules are the RetrieveRecordChangeHistory and RetrieveAuditDetails functions, and any others that return audit details. The results returned for these messages will include all the columns from the audit table as well as the oldvalues and newvalues columns, which include a JSON format value that contains the values that were changed.

API Request Limits

Using a CROSS APPLY to execute a message for each row in a table could lead to a lot of API requests, and restrictions will be coming into force on how many you’re allowed to make. Take care not to overload your account using this pattern. There are safety limits built in to SQL 4 CDS that will automatically stop a query if it goes over a certain number of requests. By default this is turned on and set to 100 requests, and I’d recommend you keep this turned on.

Query Editor Support

You can find all the supported messages in the Object Explorer pane under the Programmability folder. Here you’ll find a list of all the messages that are supported by both the TVF and stored procedure syntax.

The messages will also show up as you type in the Intellisense dropdowns:

It also shows up as an Execute Message icon in the Query Plan viewer. This is the query plan for the first example query from earlier:

JSON functions

You might have noticed in my earlier examples I was using the JSON_VALUE function to get some data out of a JSON formatted column. This release includes this and the JSON_PATH_EXISTS functions to let you query JSON data.

As well as the audit data described earlier being presented in JSON format, these functions can also come in useful for other situations such as querying the systemform.formjson column to help find forms that include particular columns or controls.

String functions

This update includes support for the CHAR/ASCII and NCHAR/UNICODE functions to convert data between characters and the corresponding codes, which is very useful when working with special characters. It also adds the DATALENGTH function so you can calculate how much space your data is taking up.

Paging

I’ve blogged several times about some of the pitfalls of paging with FetchXML. This update detects one of these situations where there are child records in the query. In these situations SQL 4 CDS will apply its own paging logic rather than the standard FetchXML paging to ensure that records aren’t silently skipped as it moves between pages.

Query Optimisations & Fixes

Finally, there’s a bunch of optimisations in this release to help you get the results to your query faster.

One interesting one is if you have a join like:

SELECT account.accountid,
       contact.contactid
FROM   account
       INNER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
          AND account.name = contact.fullname
          AND account.name = 'Data8';

FetchXML only supports using one column for a join, and in this query we need to use two. However, because the value of account.name is fixed, so too must be contact.fullname. This query will now be translated to:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='contact'>
    <all-attributes />
    <link-entity name='account' to='parentcustomerid' from='accountid' alias='account' link-type='inner'>
      <all-attributes />
      <filter>
        <condition attribute='name' operator='eq' value='Data8' />
      </filter>
    </link-entity>
    <filter>
      <condition attribute='fullname' operator='eq' value='Data8' />
    </filter>
  </entity>
</fetch>

It also now converts groupings using the YEAR, MONTH and DAY functions to use the native FetchXML date grouping options for more efficient queries – previously only the DATEPART function was supported for this.

You may also have noticed incorrect NULL results when using the MIN aggregate function which has now been fixed.