Add language and optimizer support for ISO <distinct predicate>
SQL 1999 and later include the <distinct predicate> IS [NOT] DISTINCT FROM.
The definition of distinct is (quoting from the 2003 standard) "informally, not equal, not both null." This is consistent with where SQL Server (following the standard) already uses the keyword DISTINCT. Adding <distinct predicate> to T-SQL would simplify coding of joins, in particular, and as of 2008, MERGE statements in a variety of typical scenarios.
SELECT T1.this, T2.that
FROM T1 JOIN T2
ON T1.entry IS NOT DISTINCT FROM T2.entry
Currently, this must be written as
SELECT T1.this, T2.that
FROM T1 JOIN T2
T1.entry = T2.entry
(T1.entry IS NULL AND T2.entry IS NULL)
This is a common requirement, but coding this for many columns is both tedious and error-prone (especially because of AND/OR precedence issues). Changing the setting of ANSI_NULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSI_NULLS to off is also non-standard and not granular enough to apply to specific comparisons in a single query.
Upvotes: 289<=-=Aug 9 2007 9:12AM=-=>
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.
I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.
Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this, we have to finish other commitments.<=-=Mar 11 2011 6:41AM=-=>
What is the Root Duplicate for this please? This links back to 307476 which in turn links back to here but both have been closed as duplicates.<=-=May 25 2011 3:46AM=-=>
Surely both items cannot be closed as duplicates. This makes no sense whatsoever. Please can someone at MS sort this out.<=-=Nov 15 2011 11:28AM=-=>
This must not be just a syntax shortcut and be difficult to implement. Perhaps 2012?<=-=Apr 23 2012 4:27PM=-=>
Hello Steve and others,
Due to a problem in our bug databases, there is some issue with several connect items not reflecting the status correctly. For this particular item, it is not possible to see the duplicate or the parent item.
But please be assured that the requests for IS DISTINCT predicate is our list of things to do for future & we are tracking it.
Umachandar, SQL Programmability Team
Yes, we could use this keyword.
On MERGE statements, we only want existing rows updated, if they actually changed.
The current syntax is very long and almost unreadable.
Still waiting Microsoft. It’s been 8 years and five releases (2008, 2008R2, 2012, 2014, 2016) and still no sign of this. Do you even review Connect at all?<=-=Jul 12 2016 6:36AM=-=>
We are using 2016 and we are still writing our Merge statements with MS’s crippled approach<=-=Sep 22 2016 9:30AM=-=>
How do you add temporal tables, JSON data-types and the ability to dynamically stretch tables into the world’s most advanced Cloud RDBMS, and yet not find the time to implement a basic logical operator from the 17-year-old ANSI standard?<=-=Sep 22 2016 9:34AM=-=>
It’s not just the aesthetics of the code at stake here, it’s performance. The workarounds involve either OR statements or the ISNULL function, both of which de-optimize queries in profound ways. A declarative syntax for doing this would improve execution plans around the world, saving trillions of CPU cycles and disk reads, and ultimately saving mankind. WON’T SOMEBODY THINK OF THE CHILDREN?<=-=May 18 2017 11:14AM=-=>
Don’t you love how useful M$ Connect is for raising issues? Almost 10 years go by after someone at Microsoft says this is a “Good Idea” and yet, no results ….
Jonas Hilmersson commented
Some feedback on this?