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 45
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 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 Eric [MSFT] 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 Eric [MSFT] 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.