SQL 4 CDS v7.5 Released

Lots of updates with subqueries, getting server information and more in this latest update.

More Efficient Subquery Filters

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.

Get Server Information

SQL 4 CDS now supports the @@VERSION and @@SERVERNAME global variables to let you access basic information. More is also available using the SERVERPROPERTY function:

SELECT @@VERSION,
       @@SERVERNAME,
       SERVERPROPERTY('productversion'),
       SERVERPROPERTY('collation')

New Data Type

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 DISTINCT FROM

IS [NOT] DISTINCT FROM is a new filter predicate introduced in SQL Server 2022. It’s similar to = and <>, except with how NULL values are handled.

In SQL, 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 NULL values.

This actually corresponds with the FetchXML ne operator, which will match rows that contain a NULL value:

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>

Using SetState Requests

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 Assign or 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:

• Assign
• SetState
• SetParentBusinessUnit
• SetBusinessEquipment

If other fields are included in the UPDATE statement, they will use the standard Update request as normal.

More Tweaks

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!