SQL 4 CDS v7.2 Released

This latest update includes more powerful text-handling capabilities, particularly for non-English environments, more performance improvements and confirmation prompts in Azure Data Studio.

Collation Support

Collation is the database term for how text values are compared, which is important for filtering and sorting data. Different languages have different rules for this, and SQL 4 CDS now understands the language of the environment you’re connected to and applies the appropriate rules.

SELECT   name
FROM     account
ORDER BY name

If you’re using a non-English environment you may previously have got different sorting & filtering results depending on whether your query was fully converted to FetchXML or not – now it should consistently use the rules for your language regardless of how the query gets executed.

You can also force a query to use a particular collation. For example, if you’re in an English environment, text comparisons are normally done case-sensitively, so this query:

SELECT firstname,
       LOWER(firstname),
       CASE WHEN firstname = LOWER(firstname) THEN 'Lower case' ELSE 'Not lower case' END
FROM   contact

wouldn’t produce the expected result:

Mark	mark	Lower case
DAVE	dave	Lower case
kate	kate	Lower case

Using the COLLATE option you can now force the comparison to be case sensitive:

SELECT firstname,
       LOWER(firstname),
       CASE WHEN firstname = LOWER(firstname) COLLATE Latin1_General_CS_AI THEN 'Lower case' ELSE 'Not lower case' END
FROM   contact

which produces the expected:

Mark	mark	Not lower case
DAVE	dave	Not lower case
kate	kate	Lower case

String Functions

To take advantage of the new collation support, this version also includes support for several more string functions:

• PATINDEX
• UPPER
• LOWER

You can also use the table-valued function sys.fn_helpcollations() to get a list of supported collations, and COLLATIONPROPERTY to get some more details.

Azure Data Studio Confirmations

The XrmToolBox version of SQL 4 CDS has always prompted you before changing any data via an INSERT, UPDATE or DELETE query. Now the Azure Data Studio version will do the same. Configuration options are available in the settings to control when these prompts are shown.

Automatic Parallelism

When you run an INSERT, UPDATE or DELETE statement that affects multiple records, SQL 4 CDS can automatically run many changes in parallel to improve performance. This latest version also includes an option to detect how many operations should run at the same time for best performance, using the recommendations given by the server.

To take advantage of this, go to the Settings and change the number of worker threads to 0:

I hope you find these new updates useful, and please add issues to the GitHub site for any further improvements you’d like to see!