In SQL Server is there any difference between not(

问题:

In a SQL Server where clause does it make any difference whether you code not(columnName='value') or columnName<>'value'?

I am thinking in terms of performance.

I have been told that when using Not() it might not use an index that it might otherwise use with <>.

回答1:

Best thing to do is to check the execution plans. When I test the following in SQL Server 2008 they give identical plans (and both get translated into 2 range seeks. So <> x gets converted to > x OR < x)

CREATE TABLE T
  (
     C INT,
     D INT,
     PRIMARY KEY(C, D)
  )

INSERT INTO T
SELECT 1,
       1
UNION ALL
SELECT DISTINCT 2,
                number
FROM   master..spt_values

SELECT *
FROM   T
WHERE  NOT ( C = 2 )

SELECT *
FROM   T
WHERE  ( C <> 2 )  

Gives

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=CONVERT_IMPLICIT(int,[@1],0), [Expr1004]=(10)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[@1],0), [Expr1009]=NULL, [Expr1007]=(6)))
       |                        |--Constant Scan
       |--Clustered Index Seek(OBJECT:([test].[dbo].[T].[PK__T__B86D18326339AFF7]), SEEK:([test].[dbo].[T].[C] > [Expr1010] AND [test].[dbo].[T].[C] < [Expr1011]) ORDERED FORWARD)

回答2:

The optimiser can sometimes rise eyebrows with its mad skillz at translating expressions to something different, but faster.

Say, if you select from a table with few unique values, and SQL Server has means to figure there are actually few unique values (say, 1, 2, and 3), then where x<>2 may even end up converted to someting like [Union1004] = (1) OR [Union1004] = (3), which is somewhat unrelated to the initial expression but will yield the wanted result.

That is, don't worry about this level of performance. SQL Server will mangle it anyway.

回答3:

If both arguments have a NON-NULL value - they're equivalent

BUT

Even if any has a null value - they're still equivalent, but you cannot rely on them 8-)

The mentioned above in citation is NOT TRUE, thanks for @Martin Smith

The only thing that matters and differs in performance - if you use filtered indexes, than Optimizer searches for filtered index not normalizing the condition, but simple lexical equivalence.

So, if you have index on columnName filtered with statement WHERE columnName<>'value' then in case if you write columnName<>'value' in WHERE of select - the index may be used, depending on other conditions, if you write not(columnName='value') - the index even will not be considered

AND

Don't try to help optimizer to do its job. It is very complicated, so - do not confuse it 8-) Or do it if you really know what exactly you doing and how it influences the optimizer's behavior