SQL 4 CDS v10 Released

The latest version of the SQL 4 CDS tool is here with a raft of improvements and a few key new features. Update in XrmToolBox or download the latest releases for Azure Data Studio, SSMS 20 and SSMS 21.

Common Table Expressions & Subqueries

Common Table Expressions (CTEs) have been supported in SQL 4 CDS for a while, but this release brings them into INSERT, UPDATE and DELETE statements as well as SELECT. They can simplify complex statements by splitting out the logic of where the data comes from into the CTE and leaving the logic of where it is going in the main body of the statement.

WITH  AccountsToDelete (accountid) AS (
  SELECT accountid
  FROM   account
  WHERE  ...
)
DELETE AccountsToDelete

It comes in particularly handy when you want to use window functions as part of an update. For example, if you have a lot of records with the same name and you want to number them:

WITH UniqueNames (lastname, suffix) AS (
  SELECT lastname,
         ROW_NUMBER() OVER (PARTITION BY fullname ORDER BY createdon)
  FROM   contact
)
UPDATE UniqueNames SET lastname += ' (' + CAST(suffix AS varchar(100)) + ')'
WHERE suffix > 1

You can also do the same with subqueries:

UPDATE UniqueNames
SET    lastname += ' (' + CAST(suffix AS varchar(100)) + ' )'
FROM   (
       SELECT lastname,
              ROW_NUMBER() OVER (PARTITION BY fullname ORDER BY createdon) AS suffix
       FROM   contact
       ) AS UniqueNames
WHERE  suffix > 1

versionnumber Column

Most tables (those with change tracking enabled) have a versionnumber column. If you’re using TDS Endpoint you’ll see this populated as “System.Byte[]”, but otherwise you’ll now see the actual binary value. Regardless of whether you’re using TDS Endpoint or not, you can convert this value to a number and use it to filter to find records that have changed:

-- Get the current latest version number
DECLARE @LatestVersion bigint
SELECT top 1 @LatestVersion = versionnumber FROM account ORDER BY versionnumber DESC
SELECT @LatestVersion

-- Wait for a while for users to make changes to the data...
INSERT INTO account (name) VALUES ('test')

-- Get the records that have changed
SELECT * FROM account WHERE versionnumber > @LatestVersion

As part of adding this support in, SQL 4 CDS now understands the binary, varbinary, timestamp and rowversion data types and the conversions between those and other types. You can also use binary literals and binary values are shown correctly in the results grid.

Please note that any binary results returned from TDS Endpoint are shown as “System.Byte[]” instead of the actual value – this is a bug with TDS Endpoint itself converting the binary value to a string before sending it back and not an error in SQL 4 CDS. The binary value is used within the query however, so you can convert it before returning it. For example, if you want to see the version number from TDS Endpoint you can convert it to a bigint first:

SELECT CONVERT(bigint, versionnumber)
FROM   account

Column Source Information

If you’re using the ADO.NET provider, the schema information returned by Sql4CdsDataReader.GetSchemaTable now includes the server, table and column name that each column came from.

In the XrmToolBox tool, this is exposed as a new option when formatting a query to add the display names as aliases of each column, making it easier to read the results.

SELECT a.name,
       c.fullname,
       c.lastname AS lname
FROM   account AS a
       INNER JOIN
       contact AS c
       ON c.parentcustomerid = a.accountid

SELECT a.name AS [Account Name (Account)],
       c.fullname AS [Full Name (Contact)],
       c.lastname AS lname
FROM   account AS a
       INNER JOIN
       contact AS c
       ON c.parentcustomerid = a.accountid

Any existing aliases are preserved, and if you have columns from different tables then the table display name is also included in brackets. If you have any wildcard columns (SELECT *) it won’t automatically add the aliases in.

Bug Fixes & Improvements

There are a lot of more minor fixes & improvements in this release. They are listed in detail in the release notes, but to highlight a few:

• Fixed a regression causing an error when sharing a record by inserting into the principalobjectaccess table. This allows the example shared in a previous post to work again.
• Fixed a bug causing the entire XrmToolBox process to crash when using a custom FetchXML datetime filter operator, such as SELECT * FROM account WHERE createdon = lastxdays(3)
• Fixed a bug causing the results of the OPENJSON table valued function to be cached incorrectly leading to duplicated results when the function is applied to another table. See the original bug report for an example of a useful query utilising this function!

I have also had some reports of bugs around updating or deleting records in bulk with multithreading options enabled. I haven’t been able to reproduce these in order to check, but I believe this may be fixed in this release, please let me know!

Feedback

As always, if you encounter any problems or have any suggestions for further improvements, please let me know by creating an issue on GitHub.

If you are enjoying SQL 4 CDS and find it helps save you time with working with Dataverse, please take a few moments to share your experiences and queries on social media, give it a quick review on the XrmToolBox tool store, or show your appreciation with a donation!