SQL 4 CDS v7.4 Released

This latest update lets you access data in the preview long-term retention feature along with lots of performance improvements and bug fixes.

Long-Term Retention Data

The much-anticipated option to move historic data to a long-term retention store arrived in public preview last month – check out the official docs and Matt Beard’s writeup if you haven’t tried it yet.

This new version of SQL 4 CDS lets you access any data in this new store by using the archive schema. For example:

SELECT contactid,
       fullname
FROM   contact
WHERE  ...

SELECT contactid,
       fullname
FROM   archive.contact
WHERE  ...

Not all tables are enabled for long-term retention – only the enabled tables will appear in the archive schema. The Object Explorer view now includes a new Long Term Retention folder that shows the tables that are available:

Aggregate Query Improvements

You can now use the STRING_AGG function in your queries to easily combine strings from multiple records together. For example, to get a list of accounts and a comma-separated list of the contact names in each account:

SELECT   account.name,
         STRING_AGG(contact.fullname, ', ')
FROM     account
         LEFT OUTER JOIN contact ON account.accountid = contact.parentcustomerid
GROUP BY account.name

The MIN and MAX functions now work on primary key and lookup columns, which can come in useful for picking an arbitrary record within each group. You’ll also now get a clearer error message if you try to use SUM or AVG on non-numeric data.

Join Improvements

Many thanks to GitHub user hiroyuki0415 for raising a number of issues about how SQL 4 CDS processes joins in some cases – this update brings various fixes in this area when a join can’t be translated to FetchXML because of complex join criteria. Thanks also to Hugo Kornelis for his SQL Server Execution Plan Reference which has been invaluable in this area.

You may notice that the FetchXML that is generated for joins with filters is different in some cases. SQL 4 CDS will now move more filters into the <link-entity> element rather than leaving them in the root <entity> element to make the FetchXML more readable. This also produces more reliable results with some entity types that don’t play nicely with filters on related tables.

On similar lines, you might also see cases where an outer join magically becomes an inner join in the FetchXML. For example, this query:

SELECT account.name,
       contact.fullname
FROM   account
       LEFT OUTER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
WHERE  contact.firstname = 'Mark'

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

Because the filter condition means there must be a related contact record for the account record to be included in the results, the join type is automatically changed to an inner join. This makes the FetchXML easier to read and works better with some of those stranger entity types.

Finally, joining tables from two different instances now works correct when using text fields, for example:

SELECT uat.name,
       uat.telephone1,
       prod.telephone1
FROM   uat..account AS uat
       INNER JOIN
       prod..account AS prod
       ON uat.name = prod.name
WHERE  uat.telephone1 <> prod.telephone1

This query will compare the account records between the UAT and PROD instances and show those with the same name but different telephone numbers.

ADO.NET Provider Update

For those using the ADO.NET provider to use SQL 4 CDS commands in your own applications, the minimum .NET version has increased to .NET 6. This keeps SQL 4 CDS in line with the latest updates of the ServiceClient class from Microsoft.

If you’re using .NET Framework, the provider will still work on .NET Framework 4.6.2 as before.

As always, please let me know if you encounter any problems with this version of SQL 4 CDS by opening an issue on GitHub. Don’t forget to rate it ⭐ on XrmToolBox if you find it useful, and donations are always appreciated!