SQL 4 CDS 6.4 Released

I’ve just published SQL 4 CDS 6.4 with support for earlier versions of CRM and more complex queries. I’ve also been setting up a wiki with more detailed information.

Wiki Documentation

I’ve set up a wiki on the GitHub site to include some much more detailed documentation about the supported SQL syntax and how to use more of the advanced features of SQL 4 CDS to query and modify your data in Dataverse / Dynamics 365. It’s a work in progress and I’d love your feedback on what you’d like to see to make working with SQL 4 CDS easier.

On Premise Dynamics CRM Support

SQL 4 CDS has always supported on-premise instances, but this release extends the versions it works with. It should now work with all versions from Dynamics CRM 2011 onwards!

I don’t have all versions available to test with so if you do encounter any errors connecting to earlier versions please let me know.

Subquery Improvements

Daryl LaBar posted recently showing how to quickly enable or disable plugins in bulk using SQL 4 CDS. The query he showed was:

UPDATE sdkmessageprocessingstep
SET    statecode  = 0,
       statuscode = 1
WHERE  sdkmessageprocessingstepid IN (SELECT sdkmessageprocessingstepid
                                      FROM   sdkmessageprocessingstep
                                      WHERE  plugintypeid IN (SELECT plugintypeid
                                                              FROM   plugintype
                                                              WHERE  pluginassemblyid = '95858c14-e3c9-4ef9-b0ef-0a2c255ea6df')
                                             AND statecode = 1);

This works fine, but exposed an inefficiency in how SQL 4 CDS processes the nested IN subqueries. This update can now convert this to a single FetchXML query for faster processing.

While I’m on the topic I’d also like to offer this simplified query to do the same job based on the name of the name of the plugin assembly so you don’t have to find the guid:

UPDATE sdkmessageprocessingstep
SET    statecode  = 0,
       statuscode = 1
FROM   sdkmessageprocessingstep
       INNER JOIN
       plugintype
       ON sdkmessageprocessingstep.plugintypeid = plugintype.plugintypeid
       INNER JOIN
       pluginassembly
       ON plugintype.pluginassemblyid = pluginassembly.pluginassemblyid
WHERE  pluginassembly.name = 'MarkCarrington.Dev.Plugins'
       AND sdkmessageprocessingstep.statecode = 1

This update also fixes “ambiguous column” errors when using IN and EXISTS subqueries, and an error that could result in multiple rows being returned incorrectly when using a scalar subquery. This fixes queries like:

SELECT name,
       (SELECT TOP 1 fullname
        FROM   contact
        WHERE  parentcustomerid = account.accountid)
FROM   account

Join Performance Improvements

The query optimizer will now select a more efficient merge join rather than a hash join for more queries. Joins which can’t use either of these join types will use a nested loop, and these are also improved by caching the data in the inner loop to give a massive performance boost to these queries.

Aggregate Queries

Some aggregate queries could fail with an “Object reference not set to an instance of an object” error, especially if MIN or MAX was being used and all the values in a group were NULL. These queries will now produce the correct values.