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.
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 ANSINULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSINULLS to off is also non-standard and not granular enough to apply to specific comparisons in a single query.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 289
<=-=Aug 9 2007 9:12AM=-=>Benefits:
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.
Best regards,
Eric
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.
Thanks
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 ….
7 comments
-
Anonymous
commented
I really think that ANSI be damned, the syntax IS [NOT] is much more readable than IS [NOT] DISTINCT FROM
Definitely this should be implemented in T-SQL asap, it's already implemented in the query engine, there's just no easy way of writing it. -
Anonymous
commented
Posting as a result of reading this article by Itzik
I particularly like his suggestion for a IS & IS NOT syntax
-
Andrew Hill
commented
even the query processor has this concept and is able to optimise queries to use it -- select * from s join t on s.pk=t.pk where exists (select s.* intersect t.*) shows an internal "is" operator, not "eq", and treats nulls as equal.
However, presuming on the query optimiser here is not a good thing, as in non-trivial examples it isn't able to roll it into the form which the standard says i can use.
-
Chojrak
commented
This is an utmost example of ignoring clients. I'm stunned they've rejected it despite lots of upvotes, and because I know that this suggestion requires negligible amount of time to develop. It's not a new storage engine or successor to in-memory database. It's a small operator, useful as ****.
SQL Server team, don't you realize all these millions of SQL Server developers lose huge amount of productivity by your resistance to implement useful stuff like this one? Employ some more devs and improve this miserable T-SQL at last.
-
Marco A Zamora
commented
-
Marco A Zamora
commented
For the last month or so I've been coding features for hidden Markov models, wrangling datasets with real and dirty hierarchies, which means a *lot* of NULLs flying back and forth.
Having this operator would have saved me 20-33% of the effort in code review simplification and testing. That plus time saved having queries run in less than half the time by avoiding query plans that compute scalars.
-
Jonas Hilmersson
commented
Some feedback on this?