1

Does SQL Server have an operator equivalent to the Ansi SQL operators : IS DISTINCT FROM / IS NOT DISTINCT FROM ?

I know I can replace : 

value1 is not distinct from value2

with : 

(value1 = value2) or (value1 is null and value2 is null)

or : 

coalesce(value1, -1) = coalesce(value2, -1)

But these options will prevent the engine to use an index, so does SQL Server have an specific operator to check this comparison ?.

Thank you.

GMB
  • 216,147
  • 25
  • 84
  • 135
Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
  • 2
    The first rewrite is correct and does not *generally* prevent the use of an index; that would depend on the further context of your query and what you've indexed. Comparing `COALESCE`d values does, so avoiding that is a good idea. – Jeroen Mostert Feb 25 '20 at 14:06
  • Yes, it does @HABO. I wanted to avoid it, because it's very verbose. But looks like there is no way around it. – Marc Guillot Feb 26 '20 at 07:17

1 Answers1

4

No.

Unfortunately, SQL Server does not implement the null-safe operator, unlike other databases such as Postgres (wich suports the standard IS DISTINCT FROM operator), or MySQL (that has an extension operator called <=>).

So you are basically stucked with the following construct:

(value1 = value2) or (value1 is null and value2 is null)
GMB
  • 216,147
  • 25
  • 84
  • 135