Lots of updates with subqueries, getting server information and more in this latest update.
You’ve been able to use subqueries for a long time with SQL 4 CDS, but this update brings some significant performance improvements to some of the ways they can be used.
SELECT * FROM contact WHERE parentcustomerid = (SELECT accountid FROM account WHERE name = 'Data8')
This type of query, using a subquery in the WHERE clause that should return a single value, will see the biggest improvement. Earlier versions would typically have retrieved all the contacts from the server and then thrown away those that don’t match. With this update, SQL 4 CDS will calculate the required account ID first, then use that in the FetchXML filter to retrieve only the matching contacts.
SELECT @@VERSION, @@SERVERNAME, SERVERPROPERTY('productversion'), SERVERPROPERTY('collation')
To support the SERVERPROPERTY function, SQL 4 CDS now also supports the sql_variant data type and the associated SQL_VARIANT_PROPERTY function. Columns of this type can contain values of any other type without losing any of the original information.
IS [NOT] DISTINCT FROM is a new filter predicate introduced in SQL Server 2022. It’s similar to
<>, except with how
NULL values are handled.
NULL is not equal to any other value, including other
NULLs. Somewhat confusingly, it’s also not not-equal to any values as well. If you’ve got a
NULL, any comparison (
>) will always be false.
IS DISTINCT FROM lets you compare
NULL values more naturally.
NULL is distinct from any non-null value, and it is not distinct from other
|1||IS DISTINCT FROM||NULL||TRUE|
|NULL||IS NOT DISTINCT FROM||NULL||TRUE|
This actually corresponds with the FetchXML
ne operator, which will match rows that contain a
SELECT * FROM contact WHERE firstname <> 'Mark'
<fetch xmlns:generator='MarkMpn.SQL4CDS' count='1000'> <entity name='contact'> <all-attributes /> <filter> <condition attribute='firstname' operator='ne' value='Mark' /> <condition attribute='firstname' operator='not-null' /> </filter> </entity> </fetch>
SELECT * FROM contact WHERE firstname IS DISTINCT FROM 'Mark'
<fetch xmlns:generator='MarkMpn.SQL4CDS' count='1000'> <entity name='contact'> <all-attributes /> <filter> <condition attribute='firstname' operator='ne' value='Mark' /> </filter> </entity> </fetch>
SQL 4 CDS will normally use standard
Update requests to make the changes required by a SQL
UPDATE statement. However, you may need to use the legacy update messages such as
SetState instead in certain cases.
To enable this you can now use the
USE_LEGACY_UPDATE_MESSAGES query hint:
UPDATE contact SET statecode = 1 WHERE ... OPTION (USE HINT ('USE_LEGACY_UPDATE_MESSAGES'))
This will force SQL 4 CDS to use the following messages instead where possible:
If other fields are included in the
UPDATE statement, they will use the standard Update request as normal.
There are various other smaller improvements in this version, such as:
- autocomplete now suggests collation names and variable names
- tool window locations are now saved, so you can keep the Object Explorer and Properties panels docked where you want them
- text values containing tabs and line breaks are now displayed better in the grid view
I hope you find this latest update useful, please keep the feedback coming on any queries it could still handle better!