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 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:
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|
To take advantage of the new collation support, this version also includes support for several more string functions:
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
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.
When you run an
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!