CODEWITHSUNDEEP
sql
KevinPaul
KevinPaul

Reputation: 105

Is <> SARGable or not? If not, what can I use instead?

I'll attempt this one more time I guess..

I've seen people saying <> is sargable, <> is not sargable, etc, and sorry, but I want clarification. And what I also want that seems impossible to get is an example of a bad query that uses <> and a more SARGable query that uses a different operator. I've seen examples about how to make a lot of queries SARGable, but no examples that use the <> operator.

I already tried to ask this once and it must be over peoples heads since they thought it wasn't a real question, but it is a real question and I want a real answer, and if I get a real answer then I can look at all of my stored procs and make better decisions on which ones to change.

Thanks..

Upvotes: 1

Views: 2795

Answers (2)

Guffa
Guffa

Reputation: 700372

The operator <> is SARGable, but depending on what you are comparing it might not help much that it is.

Ref: http://en.wikipedia.org/wiki/Sargable

"Sargable operators that rarely improve performance: <>,IN,OR,NOT IN, NOT EXISTS, NOT LIKE"

An operator like = is more likely to give good performance becauce the database can look up a single or a limited number of records from an index. When you use the <> operator, the database often has to scan the entire index to get the relevant records.

A query where the database could make some use of an index with the <> operator would be if it's a non-unique index, and there are few different values in the column so that many records can be elliminated using the index, not just a single or a few records.

Upvotes: 6

Aducci
Aducci

Reputation: 26664

Wikipedia says it is a Sargable operator that rarely improves performance

Sargable operators: =,>,<,>=,<=,BETWEEN,LIKE without leading %

Sargable operators that rarely improve performance: <>,IN,OR,NOT IN,

NOT EXISTS, NOT LIKE Non-sargable operators: LIKE with leading %

http://en.wikipedia.org/wiki/Sargable

Upvotes: 0

Related Questions