The big new feature in this update is Common Table Expression support and enhanced JSON functionality.

Common Table Expressions (CTEs) are a part of SQL that really made me rethink what is possible in the language when I first discovered them.

For those that haven’t used them before and don’t fancy trawling through the detailed documentation, there are essentially two types of CTEs, recursive and non-recursive.

Non-recursive CTEs

A non-recursive CTE is a shorthand that lets you reuse a subquery easily. For example, this query:

SELECT name
FROM   account
WHERE  primarycontactid IN (SELECT contactid
                            FROM   contact
                            WHERE  parentcustomerid IS NULL
                                   AND statecode = 0)
UNION ALL
SELECT fullname
FROM   systemuser
WHERE  systemuserid IN (SELECT ownerid
                        FROM   contact
                        WHERE  parentcustomerid IS NULL
                               AND statecode = 0)

You could rewrite this with a CTE to make it easier to understand:

WITH   orphanedcontacts
AS     (SELECT contactid,
               ownerid
        FROM   contact
        WHERE  parentcustomerid IS NULL
               AND statecode = 0)
SELECT name
FROM   account
WHERE  primarycontactid IN (SELECT contactid
                            FROM   orphanedcontacts)
UNION ALL
SELECT fullname
FROM   systemuser
WHERE  systemuserid IN (SELECT ownerid
                        FROM   orphanedcontacts);

The two queries are exactly equivalent, but the CTE version has a number of advantages. It’s easier to understand the intent of the query when you’re reading it, and if you need to make a change in future you only have to update the CTE once instead of making sure each subquery is updated in the same way.

Recursive CTEs

This is where the real magic happens. Querying any hierarchical data structure, such as parent accounts or user/manager relationships, is very difficult in SQL. You could use a query like this to find the details of a user and their manager:

SELECT user.fullname,
       manager.fullname
FROM   systemuser AS user
       INNER JOIN
       systemuser AS manager
       ON user.parentsystemuserid = manager.systemuserid
WHERE  user.fullname = 'Mark Carrington'

But what about that manager’s manager? We could add another join:

SELECT user.fullname,
       manager.fullname,
       manager_manager.fullname
FROM   systemuser AS user
       INNER JOIN
       systemuser AS manager
       ON user.parentsystemuserid = manager.systemuserid
       INNER JOIN
       systemuser AS manager_manager
       ON manager.parentsystemuserid = manager_manager.systemuserid
WHERE  user.fullname = 'Mark Carrington'

But how many levels should we add in? If we knew that we probably wouldn’t have needed to write the query in the first place. This is where a recursive CTE comes in.

WITH   userhierarchy
AS     (SELECT systemuserid,
               parentsystemuserid,
               fullname
        FROM   systemuser
        WHERE  fullname = 'Mark Carrington'
        UNION ALL
        SELECT manager.systemuserid,
               manager.parentsystemuserid,
               manager.fullname
        FROM   systemuser AS manager
               INNER JOIN
               userhierarchy
               ON userhierarchy.parentsystemuserid = manager.systemuserid)
SELECT fullname
FROM   userhierarchy

Wait, what? The CTE is referring to itself in the FROM clause. How does that work?

In any recursive CTE there are at least two parts joined with UNION ALL – the anchor part and the recursive part.

The anchor part defines a starting point for the query – one or more records that get us going on the hierarchy.

The recursive part takes each record that’s already been found and uses it to find more related records – usually the parent or the children of the current record.

The query keeps running the recursive part until it doesn’t find any more records, i.e. it reaches the top or bottom of the hierarchy.

TDS Endpoint

CTEs are standard SQL, so can’t we just use the TDS endpoint to run them? Unfortunately not, they’re explicitly blocked for some reason.

However, as we’ve seen, non-recursive CTEs can be rewritten as subqueries. SQL 4 CDS will do exactly this. You can run a query containing a non-recursive CTE and SQL 4 CDS can use the TDS Endpoint to run it using the equivalent subqueries.

A non-recursive CTE running agains the TDS Endpoint after being automatically rewritten to use subqueries

FetchXML

FetchXML has some specialised filter conditions for working with hierarchical data, which presumably translate to recursive CTEs behind the scenes. SQL 4 CDS will use these where possible, for example:

SQL

WITH   cte (systemuserid, name)
AS     (SELECT systemuserid,
               fullname
        FROM   systemuser
        WHERE  systemuserid = '45fc4284-c607-eb11-a813-000d3a654aeb'
        UNION ALL
        SELECT systemuser.systemuserid,
               fullname
        FROM   systemuser
               INNER JOIN
               cte
               ON systemuser.parentsystemuserid = cte.systemuserid)
SELECT *
FROM   contact
WHERE  ownerid NOT IN (SELECT systemuserid
                       FROM   cte)

FetchXML

<fetch xmlns:generator='MarkMpn.SQL4CDS' count='1000'>
  <entity name='contact'>
    <all-attributes />
    <link-entity name='systemuser' to='ownerid' from='systemuserid' alias='Expr7' link-type='outer'>
      <filter>
        <condition attribute='systemuserid' operator='eq-or-under' value='45fc4284-c607-eb11-a813-000d3a654aeb' />
      </filter>
    </link-entity>
    <filter>
      <condition attribute='systemuserid' entityname='Expr7' operator='null' />
    </filter>
  </entity>
</fetch>

To use these operators your CTE must:

  • reference only a single table
  • join using the columns defined as the hierarchy relationship for that table
  • not include any calculated columns

More examples

You can run more complex queries that can’t be translated directly to FetchXML or Dataverse SQL, making use of calculations in the CTE to get out more information. For example you could include the position of a record in the hierarchy:

WITH   cte (systemuserid, name, depth)
AS     (SELECT systemuserid,
               fullname,
               0
        FROM   systemuser
        WHERE  fullname = 'Mark Carrington'
        UNION ALL
        SELECT systemuser.systemuserid,
               fullname,
               depth + 1
        FROM   systemuser
               INNER JOIN
               cte
               ON systemuser.parentsystemuserid = cte.systemuserid)
SELECT *
FROM   cte

Or the path to get from the starting record:

WITH   cte (systemuserid, path)
AS     (SELECT systemuserid,
               fullname
        FROM   systemuser
        WHERE  fullname = 'Mark Carrington'
        UNION ALL
        SELECT systemuser.systemuserid,
               fullname + ' / ' + path
        FROM   systemuser
               INNER JOIN
               cte
               ON systemuser.parentsystemuserid = cte.systemuserid)
SELECT *
FROM   cte

You could also not use any tables at all and use it to calculate factorials:

WITH   Factorial (N, Factorial)
AS     (SELECT 1,
               1
        UNION ALL
        SELECT N + 1,
               (N + 1) * Factorial
        FROM   Factorial
        WHERE  N < 10)
SELECT N,
       Factorial
FROM   Factorial

When SQL 4 CDS has to execute a recursive CTE itself, it generates a rather more complex execution plan. This is the plan for the second sample query above, getting a user hierarchy including the path:

And this is the plan for the exact same query but without the path calculation, getting only the individual user names:

And finally, the plan when we can filter the anchor part of the query on a specific guid:

Although they might look pretty, simpler execution plans will mean your query can execute quicker. If you find your CTE-based query is taking a long time to run, check if you can alter it slightly to take advantage of any of these optimizations.

As so often, I’m deeply indebted to Hugo Kornelis for his detailed documentation of the internals of SQL Server. This time most of the credit has to go to his Plansplaining article on how recursive CTEs work.

Expanded JSON Support

SQL 4 CDS has had some support for JSON functions since the first release of v7. That version introduced the JSON_VALUE and JSON_PATH_EXISTS functions as a way of querying individual values from JSON strings, such as those returned from the RetrieveRecordChangeHistory function.

This release extends on this by adding the OPENJSON, JSON_QUERY and ISJSON functions. The OPENJSON function in particular can be very useful as it allows you to extract multiple values from a single JSON string.

OPENJSON

OPENJSON is a table-valued function, i.e. it returns a whole table of results rather than a single value, so you use it in the FROM clause of your query.

You can use this to find out which fields are the trigger for a Power Automate Flow:

SELECT w.name,
       trigger_params.*
FROM   workflow AS w
       CROSS APPLY
       OPENJSON (w.clientdata, '$.properties.definition.triggers.manual.inputs.schema.properties')
                WITH (title NVARCHAR (100), type NVARCHAR (100), description NVARCHAR (MAX))
                AS trigger_params
WHERE  w.category = 5

This example:

  1. takes all “Modern Flow” workflows (category 5)
  2. parses the JSON stored in the clientdata column
  3. navigates to the properties.definition.triggers.manual.inputs.schema.properties property
  4. produces an output row for each item in that JSON array, including the title, type and description properties from the JSON

You could also find out which fields are on a form by parsing the systemform.formjson column. This is a bit harder as you need to loop over all the tabs in the form, then all the columns in each tab, then all the sections in each column, then all the rows in each section, and finally all the cells in each row to find the individual fields. Fortunately we now have a tool to help us with this recursion – CTEs!

WITH cte (json) AS (
  SELECT value
  FROM   systemform
         CROSS APPLY
         OPENJSON(formjson) j
  WHERE  objecttypecode = 'account'
         AND name = 'Account'
         AND j.type in (4, 5)

  UNION ALL

  SELECT value
  FROM   cte
         CROSS APPLY
         OPENJSON(cte.json)
  WHERE  type in (4, 5)
)

SELECT JSON_VALUE(json, '$.DataFieldName')
FROM   cte
WHERE  JSON_VALUE(json, '$."$type"') LIKE '%CustomControl%'
       AND JSON_VALUE(json, '$.DataFieldName') IS NOT NULL

I’d love to hear how you’d like to use these new features, feel free to drop your example queries in the comments!

Other Improvements

As well as various fixes and improvements for some specific types of queries there are a few more usability improvements to highlight:

IGNORE_DUP_KEY

This new query hint allows you to write an INSERT statement that will silently ignore errors caused by a duplicate primary key. This can be particularly useful when adding many-to-many relationship records. For example, you can copy the security role assignments from one user to another with:

DECLARE @NewUser AS UNIQUEIDENTIFIER, @OldUser AS UNIQUEIDENTIFIER;

SELECT @NewUser = systemuserid
FROM   systemuser
WHERE  fullname = 'New User';

SELECT @OldUser AS systemuserid
FROM   systemuser
WHERE  fullname = 'Old User';

INSERT INTO systemuserroles (systemuserid, roleid)
SELECT @NewUser,
       roleid
FROM   systemuserroles
WHERE  systemuserid = @OldUser;

Normally this would cause an error if the user already has one of these roles, but by adding the IGNORE_DUP_KEY hint the query will succeed and add all the missing roles regardless:

INSERT INTO systemuserroles (systemuserid, roleid)
SELECT @NewUser,
       roleid
FROM   systemuserroles
WHERE  systemuserid = @OldUser
OPTION (USE HINT('IGNORE_DUP_KEY'))

Formatting Queries

Using the Format button in SQL 4 CDS is a quick way to tidy up your SQL code, but this has previously lost any comments in your script. This version will keep the comments, although you may need to move them back around slightly to make sure they’re exactly where you want them.

Before reformatting
After reformatting

Confirmation Messages

The confirmation message that is shown on DML queries (INSERT/UPDATE/DELETE) now has a “Yes To All” option:

This also applies to the Azure Data Studio extension:

“All” means the current batch, so if you execute 10 INSERT statements in one go you can click “Yes To All” on the first one and only be prompted once, but you’ll be prompted again the next time you execute a query.

Copy URL

You can now right-click on a record identifier in the results grid and select to copy the URL of the record rather than having to open it directly:

11 thoughts on “SQL 4 CDS v8 Released”

  1. I just updated my SQL CDS for the 26-November release, and it seems to have some issues. When attempting to copy the results to Excel, where I am trying to paste it, the application freezes. Additionally, it takes a considerable amount of time to copy the results. All of these issues started occurring after the update.

    1. Thanks for the details. I don’t think anything has changed in this area recently, but I can see that copying large amounts of data can take a long time. I’ll look into a more efficient way of doing this in a future update.

  2. I do not see the object explorer in the new release of SQL 4 CDS. No matter what I do I am not able to bring it back. is it been removed?

    1. The metadata.globaloptionset table exposes the optionset itself, but not the individual values. You can access these through the stringmap table, but to join the two you need to go via the metadata.attribute table. As multiple attributes could be using the same global optionset, you then need to DISTINCT the results:

      SELECT DISTINCT globaloptionset.name,
                      stringmap.attributevalue,
                      stringmap.attributename
      FROM   metadata.attribute
             INNER JOIN
             metadata.globaloptionset
             ON attribute.optionset = globaloptionset.metadataid
             INNER JOIN
             stringmap
             ON attribute.entitylogicalname = stringmap.objecttypecode
                AND attribute.logicalname = stringmap.attributename;
      

      Making any metadata changes isn’t currently supported.

  3. Hi Mark –

    Why is this SQL 4 CDS query results in more N:1 entities than what the actual solution show directly from the Power Apps solution browser? I’m on the latest version

    select *
    from metadata.relationship_n_1 as R
    where R.referencingentity=’Account’

    1. The solution view will only show the relationships that are part of that solution – if you added the account entity to the solution without including all metadata then there will be ones missing. If you have added all the available relationships and there are still more showing in SQL 4 CDS, could you share some details of the ones that are missing from the solution view?

  4. I have a redacted screenshot side-by-side between the solution browser and the SQL 4 CDS results. I can share that with you if you can direct me to where I can upload the screenshot.
    The SQL was generated with the following query –

    select DISTINCT
    (select D.displayname
    from metadata.entity as D
    where D.schemaname=R.referencingentity) as ThisEntity,
    E.displayname as DependsOn
    from metadata.relationship_n_1 as R
    left join metadata.entity as E
    on E.schemaname=R.referencedentity
    order by ThisEntity, DependsOn

    For now there are 14 extra entities that SQL provides versus the solution browser: Account KPI item, Currency, Facility/Equipment, Image Descriptor, Owner, Price List, Process Stage, Sales acceleration insights, Segment, Service, SLA, Team, Territory, User

    Here’s what the redacted records look like (I can send you a .csv if you can send where to upload). For now, sorry for the text formatting below (pasted from excel) –

    denormalizedattributename entitykey haschanged introducedversion iscustomizable iscustomrelationship isdenormalizedlookup ishierarchical ismanaged isrelationshipattributedenormalized isvalidforadvancedfind referencedattribute referencedentity referencingattribute referencingentity referencingentitynavigationpropertyname relationshipbehavior relationshiptype securitytypes
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 equipmentid equipment preferredequipmentid account preferredequipmentid 1 OneToManyRelationship Append
    NULL NULL NULL 8.0.0.0 1 0 NULL 0 1 0 1 externalpartyid externalparty createdbyexternalparty account CreatedByExternalParty 0 OneToManyRelationship None
    NULL NULL NULL 8.0.0.0 1 0 NULL 0 1 0 1 externalpartyid externalparty modifiedbyexternalparty account ModifiedByExternalParty 0 OneToManyRelationship None
    NULL NULL NULL 6.0.0.0 0 0 NULL 0 1 0 0 imagedescriptorid imagedescriptor entityimageid account entityimageid_imagedescriptor 0 OneToManyRelationship None
    NULL NULL NULL 1 0 1 NULL 0 1 0 1 msdyn_accountkpiitemid msdyn_accountkpiitem msdyn_accountkpiid account msdyn_accountkpiid 1 OneToManyRelationship Append
    NULL NULL NULL 9.1.2201.5003 1 1 NULL 0 1 0 1 msdyn_salesaccelerationinsightid msdyn_salesaccelerationinsight msdyn_salesaccelerationinsightid account msdyn_salesaccelerationinsightid 1 OneToManyRelationship Append
    NULL NULL NULL 1 1 1 NULL 0 0 0 1 msdyn_segmentid msdyn_segment msdyn_segmentid account msdyn_segmentid 1 OneToManyRelationship Append
    NULL NULL NULL 5.0.0.0 0 0 NULL 0 1 0 0 ownerid owner ownerid account ownerid 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 pricelevelid pricelevel defaultpricelevelid account defaultpricelevelid 1 OneToManyRelationship Append
    NULL NULL NULL 6.0.0.0 0 0 NULL 0 1 0 1 processstageid processstage stageid account stageid_processstage 2 OneToManyRelationship ParentChild
    NULL NULL NULL 5.0.0.0 0 0 NULL 0 1 0 1 serviceid service preferredserviceid account preferredserviceid 1 OneToManyRelationship Append
    NULL NULL NULL 8.1.0.0 1 0 NULL 0 1 0 1 slaid sla slaid account sla_account_sla 1 OneToManyRelationship Append
    NULL NULL NULL 8.1.0.0 1 0 NULL 0 1 0 1 slaid sla slainvokedid account slainvokedid_account_sla 1 OneToManyRelationship Append
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser preferredsystemuserid account preferredsystemuserid 1 OneToManyRelationship Append
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser modifiedby account modifiedby 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser owninguser account owninguser 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser createdonbehalfby account createdonbehalfby 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser createdby account createdby 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 systemuserid systemuser modifiedonbehalfby account modifiedonbehalfby 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 teamid team owningteam account owningteam 0 OneToManyRelationship None
    NULL NULL NULL 5.0.0.0 1 0 NULL 0 1 0 1 territoryid territory territoryid account territoryid 1 OneToManyRelationship Append
    NULL NULL NULL 5.0.0.0 0 0 NULL 0 1 0 1 transactioncurrencyid transactioncurrency transactioncurrencyid account transactioncurrencyid 0 OneToManyRelationship None

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.