sql server misc: Not Equal - Is it something to be avoided? -- sql server misc

Not Equal - Is it something to be avoided?

bstanek NO[at]SPAM bakerhill.com
10/21/2004 11:25:05 PM

sql server misc:

I am a long time database developer but not long using SQL Server. I
am now working with a performance tuning group. One of the people on
the group is instant upon not using a not equal (!=) in your where
clause. He indicates that this forces SQL Server to use a table scan
instead of indexes.

So he feels that this statement is wrong:

select id, name, label
from Product
where status != 'Disabled'

He would rather us use

select id, name, label
from Product
where status = 'Active' or status = 'Inactive' or status = 'New' or
status = 'Offered'

or to use an in clause.

It has always been my understanding that an IN clause is not something
to use and that a not equal (!=) does not disable the use of an index.

At a pure maintenace view, I cannot recommend the IN clause because of
the amount of coding changes but if it hurts performance to the degree
that he indicates, then we should change a huge portion of our
database code.

I have heard that a NOT IN is a very bad thing to use but that was
because of the expanding out to multiple OR stateements and not
because of the !=.

Can anyone help solve this point of discussion?

Thanks

Re: Not Equal - Is it something to be avoided?

Reg Besseling
11/9/2004 6:16:53 PM

Brian,

I havbe heard/read similar things in the early sybase / MS sql manuals as
far as i remember the recomended solution was something like

select id, name, label
from Product
where (status > 'Disabled' or status < 'Disabled' )

i also remember the reason for this was that with the != operator each value
was checked however the < or > reduced the text to values so if the field
was orderd it could use an index inserad of checking each row.

of course this only holds if there is some sort of index on the status field

The issue here is how does the new optimiser handel the != clause

I did some testing with your scnario on my test server and had no definite
results except that the

[quoted text, click to view]

solution was slowers with the most DB reads

hope someone else can shed some light on how the Optimiser handles the
various secnarios

Regards

Reg

[quoted text, click to view]