IN vs = in Where Clauses

Question

I try to create standardized SQL scripts for use in my SSRS reports. If I wish to have a statement such as:

Select * from mytable

and use a SQL Variable (SSRS parameter) in the where clause, is there any speed advantage to using this:

Where field = @MyField

VS.

Where field IN (@MyField)

I know the second option supports both multiple selections and single selections while the first supports only single.

Are there any performance hits if I write all my queries with the IN statement for uniformity?

Answer 1

There is no performance hit. The optimizer converts the IN to a series of = operations ORed together.

As a side note, I hope you're not really using SELECT * in your actual queries.

Answer 2

For one argument optimizer will change that IN into =, in real execution statement.

As both of this arguments are Sargeble (Joe Stefanelli thanks for attention); the difference in performance may by found where you have sequence value in in clause, for example IN (1,2,3,4,5,6) is slower that between 1 and 6, because for the IN the index has to be read for each value in this case 6 time for between only once.

Answer 3

the performance does get slow if you have a large no of item in your IN clause