Add language and optimizer support for ISO <distinct predicate> - by Steve Kass

Status : 


Sign in
to vote
ID 286422 Comments
Status Active Workarounds
Type Suggestion Repros 48
Opened 7/7/2007 12:30:11 PM
Duplicates 307476 Access Restriction Public


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

Currently, this must be written as

SELECT T1.this, T2.that
ON (
  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.
Sign in to post a comment.
Posted by Jason Thorn on 5/18/2017 at 11:14 AM
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 ....
Posted by MarkRendle on 9/22/2016 at 9:34 AM
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?
Posted by MarkRendle on 9/22/2016 at 9:30 AM
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?
Posted by David Hoist on 7/12/2016 at 6:36 AM
We are using 2016 and we are still writing our Merge statements with MS's crippled approach
Posted by B Aiken on 7/28/2015 at 8:46 AM
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?
Posted by HenrikStaunPoulsen on 8/15/2012 at 12:23 AM
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.
Posted by Umachandar Jayachandran - MS on 4/23/2012 at 4:27 PM
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
Posted by Randy in Marin on 11/15/2011 at 11:28 AM
This must not be just a syntax shortcut and be difficult to implement. Perhaps 2012?
Posted by Gary Varga on 5/25/2011 at 3:46 AM
Surely both items cannot be closed as duplicates. This makes no sense whatsoever. Please can someone at MS sort this out.
Posted by Martin Smith on 3/11/2011 at 6:41 AM
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.
Posted by Microsoft on 10/17/2007 at 2:06 PM
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.
Posted by Microsoft on 8/27/2007 at 6:13 PM
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.

Best regards,
Posted by SAinCA on 8/9/2007 at 9:12 AM
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.