SQL 4 CDS v9.6 released

Lots of new possibilities for working with your Dataverse data with this latest update!

This new version introduces three more advanced SQL options that open up a lot of new possibilities – I’d be really interested to hear where you start using them!

• Cursors
• Temporary Tables
• Window Functions

These really bring the SQL 4 CDS engine way beyond where I’d originally envisaged as a way to quickly translate basic SELECT statements into Fetch XML and I hope you find them useful.

Cursors

Database people, don’t start yelling at me yet! We should normally be using other options where possible, but sometimes a cursor unlocks some extra possibilities.

For those not familiar with the concept, a cursor allows your SQL script to operate on rows one-by-one rather than as a set, so you can loop over the results of a query and do some operation separately on each row.

Generally this is less efficient and should be avoided where possible, but can be very useful in the right circumstances.

-- Declare a cursor to read users - only include active licensed users
DECLARE UserCursor CURSOR
FOR
SELECT systemuserid, fullname
FROM   systemuser
WHERE  isdisabled = 0
       AND accessmode = 0

-- Open the cursor ready for reading
OPEN UserCursor

-- Declare variables to hold the data from the cursor
DECLARE @userid UNIQUEIDENTIFIER, @name NVARCHAR(100), @count INT

-- Get the first row from the cursor
FETCH NEXT FROM UserCursor INTO @userid, @name

-- Keep executing a loop for each row we can read
WHILE @@FETCH_STATUS = 0
BEGIN

  -- Impersonate the user
  EXECUTE AS USER = @userid

  -- Run a query as the user to check how many accounts they can access
  SELECT @count = COUNT(*)
  FROM account

  -- Stop impersonating the user
  REVERT

  -- Output the results
  PRINT @name + ' can read ' + CONVERT(VARCHAR(10), @count) + ' accounts'

  -- Move on to the next row
  FETCH NEXT FROM UserCursor INTO @userid, @name
END

This produces some output like:

Impersonated user 6e00e8f6-496b-ea11-a812-000d3a0b8c16

Reverted impersonation
Mark Carrington can read 56066 accounts

Impersonated user 7400e8f6-496b-ea11-a812-000d3a0b8c16

Reverted impersonation
John Robb can read 0 accounts

Impersonated user b7e68613-74b8-4c05-9ff4-8c3c418feb1a

Reverted impersonation
Sara Smith can read 56066 accounts

Completion time: 2025-02-14T08:38:03.3162333+00:00

For those familiar with cursors in SQL Server, there are various restrictions on the types of cursors that SQL 4 CDS supports. Most importantly, only STATIC READ_ONLY cursors will work – any cursors declared as KEYSET, DYNAMIC, FAST_FORWARD, SCROLL_LOCKS, OPTIMISTIC or FOR UPDATE will generate an error.

Temporary Tables

You can now create and use temporary tables to store data in your scripts. The output of the script above was a bit messy, so we could tidy it up by inserting the results for each user into a temporary table and then SELECTing the results at the end to view them in a standard grid view:

-- Create the temporary table to hold the results
CREATE TABLE #UserResults (
  systemuserid UNIQUEIDENTIFIER,
  accounts INT
)

-- Declare a cursor to read users - only include active licensed users
DECLARE UserCursor CURSOR
FOR
SELECT systemuserid FROM systemuser WHERE isdisabled = 0 and accessmode = 0

-- Open the cursor ready for reading
OPEN UserCursor

-- Declare variables to hold the data from the cursor
DECLARE @userid UNIQUEIDENTIFIER, @count INT

-- Get the first row from the cursor
FETCH NEXT FROM UserCursor INTO @userid

-- Keep executing a loop for each row we can read
WHILE @@FETCH_STATUS = 0
BEGIN

  -- Impersonate the user
  EXECUTE AS USER = @userid

  -- Run a query as the user to check how many accounts they can access
  SELECT @count = COUNT(*)
  FROM account

  -- Stop impersonating the user
  REVERT

  -- Saved the results in the temporary table
  INSERT INTO #UserResults (systemuserid, accounts)
  VALUES (@userid, @count)

  -- Move on to the next row
  FETCH NEXT FROM UserCursor INTO @userid
END

-- Output the results by combining the data in the temporary table and the main systemuser table
SELECT   u.fullname, r.accounts
FROM     systemuser u
         INNER JOIN #UserResults r
         ON u.systemuserid = r.systemuserid
ORDER BY r.accounts DESC

-- Clean up the temporary table
DROP TABLE #UserResults

and we get the much nicer formatted results:

Again, there are a lot of options that SQL Server supports but SQL 4 CDS does not. This only aims to provide basic support for tables to store a small amount of simple data. If there are more options you need from SQL Server that aren’t yet implemented, please let me know.

Window Functions

Another feature you might not need every day but can be really helpful on occasion. These let you rank rows within your dataset or include aggregate values alongside individual rows.

SELECT name,
       industrycodename,
       revenue,

       -- Use RANK to generate a number indicating the revenue ranking for each account
       RANK() OVER (PARTITION BY industrycodename ORDER BY revenue DESC) AS revenue_rank_by_industry,

       -- Get the total revenue across all accounts in the same industry
       SUM(revenue) OVER (PARTITION BY industrycodename) AS total_revenue_for_industry,

       -- Get the total revenue of this and all higher-revenue accounts in the same industry
       SUM(revenue) OVER (PARTITION BY industrycodename ORDER BY revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue_for_industry
FROM   account

The ranking functions:

• ROW_NUMBER
• RANK
• DENSE_RANK

help you find the best/worst records within each group, while applying the OVER clause to regular aggregate functions:

• AVG
• COUNT
• MIN
• MAX
• SUM

lets you get aggregate values without losing the detail of each individual row.

This is also only a partial implementation of the range of options supported by SQL Server. Specifically the NTILE function is not supported, or the RANGE clause or the UNBOUNDED FOLLOWING options. Again, please let me know if you would get value from any of these missing options.

Environment Highlighting

The XrmToolBox version of SQL 4 CDS now remembers which connection each of your query tabs was using when you re-open it. To help you find the right tab again quickly, each one has a colour-coded icon to match the environment highlight colour:

The Object Explorer pane also keeps the colour coding going:

Thanks

I owe a big thanks to Hugo Kornelis for the details about cursor [more] and window function [more, and more] execution plans that made this release possible. Please check out his blog if you want to get more information about how these functions are implemented in SQL Server, which I have shamelessly attempted to duplicate in SQL 4 CDS. Any errors in that implementation however are entirely my own.

I hope you find the new features useful. If you have other improvements you would like to see in SQL 4 CDS or encounter any errors, please let me know on GitHub. If you find the tool useful I always appreciate a ⭐ review on XrmToolBox and I also now accept ❤️ sponsorship on GitHub.