>I compared two approaches in WHERE clause.
>
>The first one was about using a regular syntax such as WHERE Client.NoProvince=1 OR Client.NoProvince=2 while the other one was using the IN approach such as WHERE Client.NoProvince IN (1,2).
>
>When I analyzed the execution plan, I see the first approach used the index seek at 51% with an aggregate at 49%. The second approach used the index seek at 62% with an aggregate at 38%. The second approach, however, uses a parallelism under 1%.
>
>So, here's the question or concerns:
>
>1. While the IN approach is cleaner, is it as optimized (the scenario seems to indicate so but best practices may apply)
>2. Is the IN syntax SQL Ansi standard
>3. Is the usage of the parallelism approach any factor at all
>
>Which one do you prefer? Which one do you use?

If, in practice, you can write one of them, then I don't think it matters (too much) - and actually AFAIK under the covers an IN query is treated as an (x = ... OR x = ...) query and is a shorthand notation, surprised that you are seeing different plans, maybe changed in recent versions. If you are looking it from ANSI SQL perspective, yes IN syntax is ANSI SQL, but beware that MS SQL server has its own extension to standard and thus NULL in the values wouldn't behave as it should with any ANSI SQL database. Based on settings, MS SQL would simply accept x=NULL comparison as if you did 'x is NULL' and that is not an ANSI SQL. Thus, (based on settings) IN (...., NULL) would be treated as x is = to one of the values or x is null.

My preference is really neither, but an EXISTS query. Ibn practice I would think the values are coming from another table and thus EXISTS (or an inner join) query sounds to be better. If you are hardcoding the values then I think it doesn't matter which one you use (hardcoded values might make sense when they are enumeration values).