Home Dashboard Directory Help
Search

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


Status: 

Active


219
1
Sign in
to vote
Type: Suggestion
ID: 286422
Opened: 7/7/2007 12:30:11 PM
Access Restriction: Public
Duplicates: 307476
1
Workaround(s)
view

Description

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.

Example:

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
ON (
T1.entry = T2.entry
OR
(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.
Details
Sign in to post a comment.
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.

Thanks
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,
Eric
Posted by SAinCA on 8/9/2007 at 9:12 AM
Benefits:
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.
Sign in to post a workaround.
Posted by Paul White NZ on 2/2/2012 at 1:57 AM
SELECT T1.this, T2.that
FROM T1 JOIN T2 ON
    EXISTS( SELECT T1.entry INTERSECT SELECT T2.entry);

http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx