SQL Server - Nullable column comparisons needed?
It is very common to need to compare the values in two nullable columns, without changing the ansi nulls setting. For example, it is common to have to write:
WHERE (a.SomeColumn IS NULL AND b.SomeColumn IS NULL) OR (a.SomeColumn = b.SomeColumn)
Given how common this is (particularly for some forms of optimistic concurrency checks that aren't based on rowversions), there should be a simple way to express the intent. I also presume the optimizer could also potentially take advantage of knowing the real intent of the statement.
A new operator is needed, to enable syntax like:
WHERE a.SomeColumn MATCHES b.SomeColumn
It could potentially be something like:
==
EQUALS
IS_EQUIVALENT_TO
or perhaps expressed in a negative form like:
DIFFERS_FROM
Perhaps we could even make a play on the "int?" used by .net generics and make it:
WHERE a.SomeColumn ?= b.SomeColumn
If you think this is needed, this is where you vote: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=307476