Hello

I have just read that the "=" has a better performance than "<>", which makes me think that I should use "NOT =" when trying to express inequality. Now, if it is so, why the query engine does not do that internally? In other words, should not the operators "=" and "<>" perform the same?

Thanks a lot.

Apart from the fact that this syntax is not allowed, we will never know what the query engine is really doing internally. Also, what would be the difference between NOT = and <>, if it was permitted at all?

I don't think it is correct to say that one operator has "better performance" than another. Actually I think an operator as such has no performance aspect at all. It depends on your query, what you want to see as the result and what your data looks like.

Well, I have read here http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx that operators influence how fast the query perform.

Now, when I wrote "NOT = " I tried to simplify writing "NOT (Value1 = Value2)".

For performance it is probably better to split "value1 <> value2" like this: "value1 < value2 AND value1 > value2"

In this way SQL has better change to benefit from indexes (if exists).

HanShi:

For performance it is probably better to split "value1 <> value2" like this: "value1 < value2 AND value1 > value2"

In this way SQL has better change to benefit from indexes (if exists).

Did you mean this?

value1 < value2 or value1 > value2

Madhivanan:

Did you mean this?

value1 < value2 or value1 > value2

Yes, off course, my mistake

i did the following query , with execution plan. i comment every query with the result of plan:

using "=" 

--index seek
--relative to batch 11%
--subtree cost 0.003
--estimated IO cost 0.003
--estimated rows: 1

select * from Production.Product
where ProductID =5

-----------------------------------------------------------------------

using "<>"

--index seek

--relative to batch 44%
--estimated IO cost 0.012
--subtree cost 0.012
--estimated rows: 503

select * from Production.Product
where ProductID <>5
-----------------------------------------------------------------------------

using < value1 or > value2

--index seek
--relative to batch 44%
--estimated IO cost 0.012
--subtree cost 0.012
--estimated rows: 503
select * from Production.Product
where ProductID <4
   or ProductID >6
 

conclusion:

- all cases activate index seek  , so no performance lost

-you find "<>"  equivalant to "< value or > valu" in performance.

- using "<>" operator  has no disadvantage , the only  effect is the expected number of rows and consequently estimated IO cost

- it seems "=" better , but realyy it is not , bez it returns only one row , with 0.003 cost , but others returm more than one row and consequently do more IO

-if query return less rows , that is best IO

 
so, don't bother yourself with  "="  or  "<>"