It’s time for another big update to SQL 4 CDS – this one’s been in the works for 3 months and has got some great improvements to its level of SQL support, performance, integration with SSMS and reusability in custom apps.
You can now declare and use variables within your SQL scripts to make them easily reusable. By putting changeable values in variables at the start of your script you can make it much easier to reliably change them and re-run the script with new values.
-- Define the variable names and types DECLARE @accountname varchar(100), @firstname varchar(100), @lastname varchar(100); -- Set a variable to a fixed value SET @accountname = 'Data8'; -- Set a variable to a value from your data SELECT @firstname = c.firstname, @lastname = c.lastname FROM account AS a INNER JOIN contact AS c ON c.parentcustomerid = a.accountid WHERE a.name = @accountname;
As well as declaring your own variables you can also use the built-in
@@ROWCOUNT global variables.
@@IDENTITY is slightly different in SQL 4 CDS than SQL Server as it is an
EntityReference type rather than an integer. The
EntityReference type can be implicitly converted to a
uniqueidentifer wherever a standard guid value is required.
By capturing the unique ID of a newly added record you can create scripts to add related records as well, e.g. add an account and related contact:
-- Define the variable names and types. Initial values can also be set at this point too DECLARE @accountid AS UNIQUEIDENTIFIER, @contactid AS UNIQUEIDENTIFIER, @accountname AS VARCHAR (100) = 'Data8', @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington'; -- Create a new account record INSERT INTO account (name) VALUES (@accountname); -- Save the newly created accountid to a local variable SET @accountid = @@IDENTITY; -- Create a new contact record linked to the new account INSERT INTO contact (parentcustomerid, parentcustomeridtype, firstname, lastname) VALUES (@accountid, 'account', @firstname, @lastname); -- Save the contactid too SET @contactid = @@IDENTITY; -- Set the primary contact on the account UPDATE account SET primarycontactid = @contactid WHERE accountid = @accountid;
You can now also use
GOTO statements to make more complex scripts. This can be particularly useful for scripts you want to reuse and where you currently need to check the state of the existing data before you run it. For example, if you want to make sure a record exists and only create it if it’s missing:
DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington'; IF NOT EXISTS (SELECT * FROM contact WHERE firstname = @firstname AND lastname = @lastname) BEGIN INSERT INTO contact (firstname, lastname) VALUES (@firstname, @lastname); END
or you might want to make sure you’ve got at least 100 test contacts available in your dev system:
DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington', @requiredcount AS INT = 100, @currentcount AS INT; SELECT @currentcount = count(*) FROM contact; WHILE @currentcount < @requiredcount BEGIN INSERT INTO contact (firstname, lastname) VALUES (@firstname, @lastname); SET @currentcount += 1; END
You can combine this with the
@@ROWCOUNT global variable to perform an upsert operation:
DECLARE @name AS VARCHAR (100) = 'Data8', @websiteurl AS VARCHAR (100) = 'https://www.data-8.co.uk'; UPDATE account SET websiteurl = @websiteurl WHERE name = @name; IF @@ROWCOUNT = 0 INSERT INTO account (name, websiteurl) VALUES (@name, @websiteurl);
You can use the
DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington', @requiredcount AS INT = 100, @currentcount AS INT; SELECT @currentcount = count(*) FROM contact; IF @currentcount >= @requiredcount BEGIN PRINT 'Already have ' + CONVERT(varchar, @currentcount) + ' contacts, no need to add more'; END ELSE BEGIN PRINT 'Only have ' + CONVERT(varchar, @currentcount) + ' contacts, inserting ' + CONVERT(varchar, @requiredcount - @currentcount) + ' more...'; WHILE @currentcount < @requiredcount BEGIN INSERT INTO contact (firstname, lastname) VALUES (@firstname, @lastname); SET @currentcount += 1; END END
When you’re adding records and want to include values from other records already in your system, you can now use subqueries:
INSERT INTO contact (parentcustomerid, parentcustomeridtype, firstname, lastname) VALUES ( (SELECT TOP 1 accountid FROM account WHERE name = 'Data8'), 'account', 'Mark', 'Carrington' )
A lot of work as gone on in the background to make queries run more efficiently, especially where a join can’t be converted into FetchXML. One good example is trying to find the solutions that contain a particular entity:
SELECT s.uniquename FROM metadata.entity INNER JOIN solutioncomponent AS sc ON entity.metadataid = sc.objectid INNER JOIN solution AS s ON sc.solutionid = s.solutionid WHERE entity.logicalname = 'account'
This query takes over 8 seconds, retrieved over 61,000
solutioncomponent and 385
solution records to produce 35 results
If you use Dynamics 365 online and have the TDS Endpoint enabled, you’ve had the option to use it to run your SELECT queries for some time. However, TDS Endpoint doesn’t support all tables and has some other limitations of the SQL syntax it supports. You might have hit errors if you had this option enabled when you run a query using something it doesn’t support.
In this update, SQL 4 CDS will first check your query to make sure it’s compatible with TDS Endpoint. If not, it will run the query using its own engine to avoid you hitting unexpected errors.
The SSMS version of SQL 4 CDS is greatly improved by this compatibility checking. In previous versions, any SELECT statement would be executed against the TDS Endpoint and produce errors if you tried to access an unsupported table. Now it will run this compatibility check and execute queries itself if TDS Endpoint doesn’t support them, so you can now run the same range of queries in both SSMS and XrmToolBox. The only exception is cross-instance queries, which still require XrmToolBox.
This update also brings the execution plan view to SSMS, so you can see how SQL 4 CDS will execute each query and get more details about FetchXML conversions.
There are a few user interface updates in the XrmToolBox version too. Converting your SQL query to FetchXML is now a little easier with the new “Convert” button in the toolbar. Click this to see your query converted to FetchXML, or click the drop-down arrow next to it to convert it to other formats instead. The previous option to convert queries to M format for use in Power BI has also moved here.
If your query needs more than one FetchXML query to run it, you’ll see each one in a separate FetchXML tab so you can copy/paste or open them in FetchXML Builder individually.
You’ll also now be prompted as you close any SQL query windows to make sure you get a chance to save your queries before it’s too late!
The internal engine is now available for developers to use in your own applications much more easily – more on that in my next post!