SQL 4 CDS v9 Released

The latest version of SQL 4 CDS is finally here! There’s too many new features and updates to list them all, but I’ll call out a few of my favourites here.

If you haven’t come across SQL 4 CDS before, it’s a tool in XrmToolBox, SQL Server Management Studio (now supporting SSMS 20 as of this release!), Azure Data Studio or a standalone library to access your Dataverse or Dynamics 365 data using standard SQL syntax. Depending on your query and settings it might use Fetch XML, Dataverse SQL or other methods to actually get your data and perform any updates, inserts or deletes.

New Fetch XML Features

The recent update to the Fetch XML documentation brought various new features SQL 4 CDS can use. You’ll see queries perform better when they use these features instead of having to perform more complex processing in the tool itself.

SELECT   TOP 100 account.name,
                 contact.fullname
FROM     account
         INNER JOIN
         contact
         ON account.primarycontactid = contact.contactid
WHERE    (EXISTS (SELECT *
                  FROM   contact
                  WHERE  parentcustomerid = account.accountid
                         AND firstname = 'Mark')
          OR industrycode IN (SELECT industrycode
                              FROM   account
                              WHERE  createdon > '2024-04-01'))
         AND account.createdby = contact.createdby
ORDER BY contact.fullname, account.name;

This makes use of the new filter types for child records, cross-table column comparisons and more flexible sort ordering. In the previous version the execution plan for this query looked rather complex. SQL 4 CDS had to retrieve a lot of records and do a lot of expensive processing itself:

and took 17 seconds to execute on my test system. With the latest updates this is simplified massively:

The whole query is now converted to a single Fetch XML query and executes in under 1 second.

Just-In-Time Compilation

Previous versions of SQL 4 CDS attempted to validate and create an execution plan for the entire SQL batch up-front. This update takes a just-in-time approach, allowing you to use an IF statement to check whether specific tables or columns exist as part of a larger script:

IF EXISTS(SELECT * FROM metadata.entity WHERE logicalname = 'new_customentity')
BEGIN
  SELECT * FROM new_customentity
END

If new_customentity doesn’t exist, this script will now run without errors. You can use this behavior to create more complex scripts that you can execute across multiple different environments that might not all have the same solutions installed.

Error Handling

Speaking of errors, SQL 4 CDS now handles errors and allows you to raise your own in a much closer way to SQL Server. You can now use TRY and CATCH to handle errors and THROW or RAISERROR to trigger your own. Each error is assigned an error code which are mapped as closely as possible to those used by SQL Server. For example, you could handle a duplicate key error like:

BEGIN TRY
  INSERT INTO new_customentity (new_alternatekeycol) VALUES ('Duplicate');
END TRY
BEGIN CATCH
  IF @@ERROR_NUMBER = 2601
    PRINT 'Duplicate';
  ELSE
    THROW;
END CATCH

When the server produces an error, SQL 4 CDS will map it to the closest possible SQL error number. It will keep the original error message as it can be more descriptive than the SQL Server error message. If SQL 4 CDS produces an error itself it will use the standard SQL Server error message text for consistency.

The error details are shown at the top of the Messages tab. In this case I’ve tried to add an account record with a too-long name. SQL 4 CDS has generated error number 2628 and shows a message highlighting the problem column and value. A quick search for “sql error 2628” brings up a wealth of resources on the causes of the error and how it can be avoided. Although these are written for SQL Server, the same principals also apply to SQL 4 CDS, so you can take advantage of decades of SQL Server experience!

If you get the error number 40517 (Keyword or statement option 'x' is not supported in this version of SQL Server) you have tried to use a SQL feature that isn’t implemented in SQL 4 CDS yet – add it as a request to the GitHub site!

Error code 10337 (An internal error occurred) is used as a catch-all. If you get this error code please report it so I can map it to the correct SQL error code.

Dapper Compatibility

If you want to query Dataverse data from your own custom .NET application, please give SQL 4 CDS a try alongside Dapper. This update makes the Sql4CdsConnection ADO.NET provider compatible with the popular Dapper micro-ORM so you can write queries like:

class Account 
{
  public string Name { get; set; }
  public string CreatedBy { get; set; }
}

private List<Account> GetAccounts(Guid user)
{
  using var con = new Sql4CdsConnection("ConnectionString");
  var accounts = con.Query<Account>("SELECT name, createdbyname As CreatedBy FROM account WHERE ownerid = @user ORDER BY createdon DESC", new { user });
  return accounts.AsList();
}

Plenty More!

There’s lot more improvements & fixes than I could possibly list here, check out the full release notes on GitHub to learn more.

Please remember to give this tool a ⭐ on the XrmToolBox tool list or a donation if you find it useful!