What’s new?

People are expecting big changes with the release of SQL 4 CDS V7 and they would be happy to know that this new feature will enable the execution of Dataverse messages in your queries along with retrieval of data from normal tables. This is big news as it opens various possibilities including working with audit data.

Querying the audit history table has been one of the most popular requests and this is the first time it’s been introduced. As you know data is stored in the audit table, however, the interface is very difficult to use. The supported way to access data is through etrieveRecordChangeHistory function which yet again is very to access especially for non-developers. Various tools like the Audit History Extractor tool in XrmToolBox are there to simplify this process.

You will be happy to know that with the release of SQL 4 CDS you can now also get to this data with SQL:

Table Valued Functions

Dataverse messages can be seen in SQL 4 CDS in two ways Stored Procedures and Table Values Functions. The image above shows the former i.e. TVF Syntax – here the Dataverse message name is: (“RetrieveRecordChangeHistory”), followed by a list of parameter values in brackets.

(“RetrieveRecordChangeHistory”) the request has two parameters:

1. The “Target” (the Id of the record to get audit history for)
2. Paging information that allows retrieval of audit history data of multiple pages.

SQL 4 CDS automatically handles paging; so, with the new release, only the first parameter is required. You can see the example below where the contacted column can be seen. The OUTERAPPLY syntax ensures that RetrieveRecordChangeHistory function is called for each contact in the list.

If you wanted to secure the history of a single contact based on its ID you can perform it in two ways:

Simply change WHERE clause as done in the image below

OR

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

In the image above, custom function using CREATELOOKUP has been used to combine the entity name “contact” with the ID of the record to get the audit history for.

Stored Procedures

You should know that DataVerse messages can also be invoked using stored procedure Syntax. This allows you to capture output values in several ways. However, remember that it cannot be used in a combination with the CROSS APPLY syntax to execute it automatically for multiple records.

Supported Messages

To be used by SQL 4 CDS, a Dataverse message must:

Have simple scalar values (number, strings, etc), or no input parameters; you should know that messages which use complex input parameters are not supported.

1. Have no input values (message will be callable as a stored procedure only, not a table-valued function)
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.
3. have only a single Entity or EntityCollection type output value of a known entity type.

Custom Actions and APIs can also be called provided if they satisfy the requirements. Remember 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.

There are a few exceptions to these rules including RetrieveAuditDetails and RetrieveRecordChangeHistory; any other option that return audit details are also included. The results on these return include all the columns from Audit table, oldvalues and newsvalues in JSON format.

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. Remember, this pattern also overload your account, so tread carefully. There are safety limits built into SQL 4 CDS which automatically put a stop to 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

See the image below to see how to find supported messages Open Programmability Folder and go to Object Explorer Pane; here the list of all messages supported by Stored Procedure Syntax and TVF will be shown.

You can see the list of messages while typing the Intellisense dropdowns:

In the Query Plan Viewer this pop-up shows up as an execute message icon.

Above, is an example of Query Plan.

JSON Functions

The new release includes JSON_PATH_EXISTS which allows you to query JSON data. This function also comes in handy for other situations including sytemform.formjson column.

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

SQL 4 CDS is very good as it does not have the paging pitfalls of its predecessors. It applies its own paging logic to check that records are not silently skipped as they move between different pages.

Query Optimization and Fixes

There are many optimisations found in this release that help you attain the results in a fast manner. See the example below:

With the new release the query will now be translates as:

It also converts groupings user DAY, MONTH AND YEAR. The functions are in native FETCHXML; moreover, MIN aggregate function has also been fixed now.