T-SQL not equal operator vs Case statement

Question

Assume I have a T-SQL statement:

select * from MyTable
where Code != 'RandomCode'

I've been tasked with making this kind of where statement perform more quickly. Books Online says that positive queries (=) are faster than negative (!= , <>).

So, one option is make this into a CASE statement e.g.

select * from MyTable
where 
   case when Code = 'RandomCode' then 0 
      else 1 end = 1

Does anyone know if this can be expected to be faster or slower than the original T-SQL ?

Thanks in advance.

I fail to see how != would be slower than =, other than having 1 extra char of overhead in the parser. In the end, at the cpu level, it's all a single cmp-type instruction with a jmp/je/jne jump afterwards. If that 1 byte does make a huge difference to your code, then whatever you're doing is way above the level of this site. — Marc B
– Marc B, Commented Aug 7, 2012 at 4:19
I very much agree with both sentiments above. That said, the CASE option looks overly complicated. Conditional where clauses should be avoided, because they can splinter the query plan (not in this case - no pun intended). — OMG Ponies
– OMG Ponies, Commented Aug 7, 2012 at 4:24
@MarcB - equality operator can be optimized with an index seek whereas inequality could only be evaluated with table/index scans. The CASE statement is essentially the same as the inequality operator, just more verbose. — Todd Li
– Todd Li, Commented Aug 7, 2012 at 4:26

Todd Li · Accepted Answer · 2012-08-07 16:03:35Z

6

You have to be more specific at what information you are interested in the table and what are the possible values of the Code column. Then you can create appropriate indexes to speed up the query.

For example, if values in the Code column could only be one of 'RandomCode', 'OtherCode', 'YetAnotherCode', you can re-write the query as:

SELECT * FROM MyTable WHERE Code = 'OtherCode' OR Code = 'YetAnotherCode'

And of course you need an index on the Code column.

If you have to do an inequality query, you can change SELECT * to a more narrow query like:

SELECT Id, Name, Whatever FROM MyTable WHERE Code != 'RandomCode'

Then create an index like:

CREATE INDEX idx_Code ON MyTable(Code) INCLUDE (Id,Name,Whatever)

This can reduce I/O by replacing a table scan with an index scan.

edited Aug 7, 2012 at 16:03

answered Aug 7, 2012 at 4:31

Todd Li

3,27923 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL not equal operator vs Case statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related