SELECT queries

The supported syntax for SELECT queries is based on the T-SQL version. Rather than reproduce that documentation entirely, refer to the Microsoft documentation in the first instance. This documentation serves to give a summary of the supported syntax and highlight any differences from the Microsoft version.

[ WITH { common_table_expression } [ ,...n ] ]
SELECT
    [ DISTINCT ]
    [ TOP ( { expression } ) [ PERCENT ] [ WITH TIES ] ]
    {
        *
        | { table_name | table_alias }.*
        | {
            [ { table_name | table_alias }. ] column_name
            | expression
            [ [ AS ] column_alias ]
          }
        | column_alias = expression
        | @variable_name = expression
    } [ ,...n ]
    [ FROM { table_source } [ ,...n ] ]
    [ WHERE { search_condition } ]
    [ GROUP BY { group_by_expression } [ ,...n ] ]
    [ HAVING { search_condition } ]
    [ UNION [ ALL ] { select_statement } ]
    [ ORDER BY { expression } [ ,...n ] ]
    [ OPTION ( { query_hint } [ ,...n ] ) ]

FROM clause

The FROM clause specifies where the data for the query comes from. This can be:

[ [ { instance_name }. ] dbo. ] { table_name } [ [ AS ] { alias_name } ] - the logical name of a Dataverse table. If instance_name is not specified, it will be loaded from the main instance SQL 4 CDS is connected to. In XrmToolBox, the instance name is the name of the connection shown in the Object Explorer pane. If this includes spaces or other special characters the name must be enclosed in square brackets, e.g. [My Dev Instance].dbo.account. If an alias name is given, this table must be referred to using that name in any other expressions. Otherwise it can be referred to using the main table name.
[ { instance_name }. ] metadata. { table_name } [ [ AS ] { alias_name } ] - a metadata table. This can be one of:
- entity
- attribute
- relationship_1_n
- relationship_n_1
- relationship_n_n
- globaloptionset If an alias name is given, this table must be referred to using that name in any other expressions. Otherwise it can be referred to using the main table name.
[ [ { instance_name }. ] dbo. ] { function_name } ( [ parameter [ ,...n ] ] ) - a call to a Dataverse message using Table Valued Function syntax. If instance_name is not specified the message will be executed in the main instance SQL 4 CDS is connected to. Values for any parameters for the function must be included within the parentheses.
{ #temp_table_name } [ [ AS ] { alias_name } ] - a temporary table
{ table_source } [ LEFT OUTER | RIGHT OUTER | FULL OUTER | INNER ] JOIN { table_source } ON { join_condition } - joins the results of two other data sources together using a condition.
{ table_source } CROSS JOIN { table_source } - joins the results of two other data sources as a full cross product.
{ table_source } [ CROSS | OUTER ] APPLY { table_source } - evaluates a subquery from the second table source in context of each row from the first table source
( { select_query } ) [ AS ] { alias_name } - evaluates another SELECT query and exposes the results using the specified alias name for use in this query
( VALUES { ( { value_expression } [ ,...n ] ) } [ ,...n ] ) [ AS ] { alias_name } ( { column_name } [ ,...n ] ) - uses a fixed set of values with the specified alias and column names.
{ table_source } [ ,...n ] - equivalent to the CROSS JOIN of all the specified data sources

Examples

Single table

SELECT name
FROM account

Aliased table

SELECT a.name
FROM account AS a

Joined tables

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

Cross-instance joins

SELECT dev.name, uat.name
FROM [Dev Instance].dbo.account AS dev
     INNER JOIN [UAT Instance].dbo.account AS uat
     ON dev.accountid = uat.accountid
AND dev.name <> uat.name

Note

See notes on cross-instance queries.

Apply

SELECT a.name, latest_contact.fullname
FROM account AS a
     OUTER APPLY (
          SELECT TOP 1 fullname
          FROM contact
          WHERE parentcustomerid = a.accountid
          ORDER BY createdon DESC
     ) AS latest_contact

Query derived table

SELECT a.name, u.fullname
FROM account AS a
     INNER JOIN (
        SELECT systemuserid, fullname
        FROM systemuser
        WHERE isdisabled = 0
     ) AS u
     ON a.ownerid = u.systemuserid

Inline table

SELECT a.industrycode, COALESCE(a.industrycodename, oldcodes.name)
FROM account AS a
    LEFT OUTER JOIN (VALUES (100, 'Old industry name')) AS oldcodes (code, name)
    ON a.industrycode = oldcodes.code

Table valued function

SELECT * FROM WhoAmI()

Note

See notes on table-valued functions below.

Table valued function with parameters

SELECT *
FROM RetrieveRecordChangeHistory(CREATELOOKUP('account', '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'))

Note

See notes on table-valued functions below.

Using Outer Apply to execute function for multiple records

SELECT contact.contactid,
       audit.createdon,
       JSON_VALUE(audit.oldvalues, '$.firstname'),
       JSON_VALUE(audit.newvalues, '$.firstname')
FROM   contact CROSS APPLY (SELECT *
                            FROM   RetrieveRecordChangeHistory(contact.contactid)) AS audit
WHERE  contact.contactid = '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'
       AND JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

Temporary table

SELECT id,
       name
FROM   #MyTempTable

Note

See notes on temporary tables.

Common Table Expressions

Common Table Expressions (CTEs) can be used to restructure queries to make them easier to read and maintain, and make it possible to query heirarchical data with recursion.

SQL 4 CDS attempts to fully implement the features of CTEs, using the heirarchical FetchXML filter operators for greater efficiency where possible. It is easy to produce a poorly-performing query using a recursive CTE. To limit the performance impact, try to ensure that:

the heirarchical CTE does not contain any calculated columns
only reference a single table in the CTE
perform the recursion on a relationship that is flagged in the Dataverse metadata as the heirarchical relationship for that table
if possible, filter the anchor part of the CTE on a single primary key guid

Examples

Poorly performing CTE

The following example does not follow the above rules and leads to a complex execution plan that can cause it to run slowly:

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

as this includes a calculated column path, references two tables systemuser and businessunit, and filters the anchor part on the record name rather than the ID. It does however perform the recursion using the standard heirarchical relationship systemuserid -< parentsystemuserid

Better performing CTE

By removing the calculated column and the additional join to the businessunit table we can significantly improve the performance of this query:

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

We lose the functionality of finding the full reporting chain for each user, so this may not be an appropriate change for all cases. Where that trade-off is acceptable however, these two changes allow SQL 4 CDS to produce a much better performing execution plan.

Best performing CTE

The final improvement we can make is to filter the anchor part on a single guid:

WITH   cte (systemuserid, fullname, buname)
AS     (SELECT su.systemuserid,
               su.fullname,
               su.businessunitidname
        FROM   systemuser AS su
        WHERE  su.systemuserid = 'fbba1533-eab2-4c37-9bae-1d246f5026e6'
        UNION ALL
        SELECT su.systemuserid,
               su.fullname,
               su.businessunitidname
        FROM   systemuser AS su
               INNER JOIN
               cte
               ON su.parentsystemuserid = cte.systemuserid)
SELECT *
FROM   cte

This allows SQL 4 CDS to translate the entire query to a single FetchXML query which can be executed efficiently by the server.

Table-valued functions

Some Dataverse messages can be invoked as table-valued functions (TVF). To be able to be invoked as a TVF, all request fields for the message (represented as input parameters to the TVF) must be scalar values (except for a single PagingInfo parameter if the output is an entity collection), and there must be at least one response field. The response field(s) must be either:

A single field of an entity-derived type, or
A single field of an entity collection, or
A single field of an array of a scalar type, or
Any number of fields of a scalar type

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SELECT queries

FROM clause

Examples

Single table

Aliased table

Joined tables

Cross-instance joins

Apply

Query derived table

Inline table

Table valued function

Table valued function with parameters

Using Outer Apply to execute function for multiple records

Temporary table

Common Table Expressions

Examples

Poorly performing CTE

Better performing CTE

Best performing CTE

Table-valued functions

Clone this wiki locally