SQL 4 CDS 2.2.0 released

I’m pleased to release SQL 4 CDS 2.2.0 with two main improvements:

Column Comparisons

As I mentioned in a separate blog post, FetchXML now supports a new method of comparing values across columns. SQL 4 CDS has been able to execute queries such as these for some time, but needed to do the work itself. It can now execute the queries more efficiently for online instances that support the new functionality.

For example, the SQL query:

SELECT count(*)
FROM   contact
WHERE  firstname = lastname;

used to produce this FetchXML query:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="contact">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="contactid" />
  </entity>
</fetch>

SQL 4 CDS would then retrieve each contact, check if the first and last names matched, and work out the count itself.

This update can now convert this query into the native FetchXML format:

<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="contactid_count" aggregate="count" />
    <filter>
      <condition attribute="firstname" operator="eq" valueof="lastname" />
    </filter>
  </entity>
</fetch>

which will execute much faster.

Bulk Updates with Joins

The ability to update records is a major feature of SQL 4 CDS. You can pull data from related records as part of the update using joins. This update fixes a bug that would cause an error when using the primary key of one entity during an update such as:

UPDATE a
SET    primarycontactid = c.contactid
FROM   account AS a
       INNER JOIN
       contact AS c
       ON a.accountid = c.parentcustomerid;

Update today from the XrmToolBox tool store to start taking advantage of these improvements!