SQL 4 CDS 3.1.0 released

I’m pleased to announce that SQL 4 CDS 3.1.0 is now available to download in XrmToolBox! This release includes a lot of performance and usability related changes.

Improved Intellisense

The list of autocomplete suggestions as you type has got a number of improvements:

• Suggestions include relevant functions as well as tables, columns and joins

• Type any part of the table/column name to see possible completions, not just the start

• Improved performance using more efficient metadata loading
• Smoother rendering

I’ve also added tooltips to show the description of tables, columns and functions. The tooltips will appear both when an item is selected in the autocomplete list and when you hover your mouse over a name in your query.

You can turn off the tooltips in the Settings dialog.

Improved insert/update/delete performance

When running bulk insert, update or delete queries, the changes to multiple records can now run in parallel for a significant performance increase.

You can control how many records will be modified at once in the Settings dialog.

Improved results display

When you run more than one query at a time, the display of the results has been improved.

Rather than having separate Results, FetchXML and Messages tabs for each query, wasting space and time, a single set of tabs is now used.

Within the Results and FetchXML tabs, the results for each query will only take up the minimal space to reduce the amount of scrolling you have to do for multiple results sets.

By default, when running a query the underlying FetchXML will now not be shown, to reduce the time taken to display the results when executing large batches of insert/update/delete requests. To view the FetchXML generated by each query, toggle the new button in the toolbar.

A single Messages log also helps improve the performance when executing large batches of requests. You can now also double-click on an entry in the Messages tab to highlight the appropriate section of the query.

If a bulk insert/update/delete query fails part way through, the Messages tab will now show the number of records that have been affected as well as the error at the point the query failed.

Impersonating users

You can now run a query as another user if you have sufficient permissions. Either click on your username in the status bar at the bottom and select the Impersonate option:

or use the SQL command:

EXECUTE AS USER = 'username'

In this case you can specify either the login name (user@domain.example) or user ID (guid) of the use to impersonate.

To go back to running queries as your own user, either click the username option in the status bar again and click Revert, or run the SQL command:

REVERT

Thanks to Marc Scheuner for the suggestion, and Jonas Rapp for the user lookup dialog!

New status bar

The status bar at the bottom of each query window has been replaced with one more closely mirroring the one in SSMS, showing key information such as:

• Status of the current query
• URL of the server this tab is connected to
• Name of the organisation database
• Username the query will be run as
• Number of rows returned by the query
• Time taken to execute the query

SQL conversion improvements

Various improvements to the conversion of SQL queries to FetchXML, including:

• Detecting distinct queries without a sort
• Handle TOP clauses over 5000 records
• Improved handling of CASE statements
• Handle BETWEEN queries without any other filters
• Fixed insert/delete of many-to-many intersect tables

There’s a lot of different improvements in this update, so please update your copy and let me know how you find it!