Some people say that the optimizer will do this internally. I asked this question of another SQL Server Expert and was told that they are processed in order, so it does make a difference. He provided the following queries as evidence:

This query will not error. The first condition is met, so the 2nd condition is never processed.

select 1 where 1=0 and cast(‘bob’ as datetime)=’hello’

This query will error since the first condition is impossible.

select 1 where 1=1 and cast(‘bob’ as datetime)=’hello’

This was shown as proof that conditions are processed in order. He said that join conditions are processed in the same way.

My initial thinking is that if the column on which the table is partitioned is listed first, SQL Server would only have consider that single partition, saving on reads of the others disks and therefore improving performance of that stored procedure and of the database overall.

When I tested this however, I was unable to see any difference in the execution plan.

If there is no difference, can you explain to me how SQL Server does this, and why the above queries operate as they do?

This is an interesting debate. I am a PeopleSoft developer and have worked extensivvely on SQLs. I think order of clause has very less impact.

To justify what I say I have a simple example: There is a big query which joins many tables and so has various mappings. Now I want this query to run conditionally. So I declare a variable outside the SQL, and use it in the SQL. Where ever I place this fairly simple condition ( substitued condition looks like ‘C’ = ‘C’ or ‘C’ = ‘F’) the query exectution doesnt change much. It takes aprox 4 secs where a false boolean is encountered.

When the amount of data to be fetched is less, on re-executing the same query many times we get different timings. This has various parameters like was the Query compiled/executed recnetly, etc.

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.