In SQL Server is there any difference between not(columnName='value') and columnName<>'value'?

Question

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 <>.

score 4 · Accepted Answer · answered Jan 11 '12 at 10:38

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)

Plan

score 3 · Answer 2 · answered Jan 11 '12 at 09:18

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.

Oleg Dok · Answer 3 · 2012-01-11T11:12:05.043

2

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

edited Jan 11 '12 at 11:12

answered Jan 11 '12 at 09:20

Oleg Dok

21,109
4
45
54

Apologies Oleg, I have clarified my question so not sure your answer is relevant now. – Simon Keep Jan 11 '12 at 09:50
Still just the same, the performance will not depend on HOW you written that, but on WHAT indexes and statistics you have – Oleg Dok Jan 11 '12 at 09:52
Thanks Oleg, so are you saying that not() will never use an index on the column but <> might use the index (dependent on all the other factors that go into that decision)? – Simon Keep Jan 11 '12 at 10:08
I say, if index declared as NOT() 0\- then query with NOT() will use it, if declared as x<>1 THEN query with x<>1 will use it - it depends not on using NOT(), but on HOW the index was created – Oleg Dok Jan 11 '12 at 10:20
@SimonKeep The stuff about filtered indexes is **not** correct. [See script here](https://gist.github.com/1594126). Both use the filtered index. – Martin Smith Jan 11 '12 at 10:45
@MartinSmith Thanks, but I surely remember that filtered indexes used regarding its lexical condition equivalence? Isn't it? – Oleg Dok Jan 11 '12 at 11:09
@SimonKeep Revise the answers, please – Oleg Dok Jan 11 '12 at 11:15
@OlegDok - Well testing shows that this is not the case at least in SP3. [I found this article which says the same as you](http://www.sqlservercentral.com/articles/Stairway+Series/72285/). Then in the [comments to the article](http://www.sqlservercentral.com/Forums/Topic1063683-2904-1.aspx) people saying it was incorrect. Maybe there used to be some issue like that when they first came out? – Martin Smith Jan 11 '12 at 11:21
@MartinSmith maybe, at least I remember it from the very first times of 2008... Looks like now is the time to revise this and to remember one thing which is always the best - DO YOURSELF A FAVOR - TRUST NOONE. Check. – Oleg Dok Jan 11 '12 at 11:24
@MartinSmith As for the article - it was written in Aug 2011 - I think I should dig deeper – Oleg Dok Jan 11 '12 at 11:25
@MartinSmith nevertheless now I think that my answer not the best, so asked the host to review the answers. Since I've already hit the reputation cap for today - it is changes a little 8-) – Oleg Dok Jan 11 '12 at 11:28
Changed the accepted answer, thanks for all your input, very useful. – Simon Keep Jan 11 '12 at 12:16

In SQL Server is there any difference between not(columnName='value') and columnName<>'value'?

3 Answers3

Linked