>>>>A quick note: "Column = @Param OR @Param IS NULL" is not the same as "@Param IS NULL OR Column = @Param".
>>>
>>>Why? It is exactly the same.
>>
>>For MSSQL's sake, I hope it isn't!
>>
>>When the first condition is true, the second is not tested. So, in the second expression, if @Param IS NULL there is no need to check on Column's value.
>
>This is not how MS SQL works query optimizer works. The order of conditions makes absolutely no difference.
I think the difference you notice, in your post, comes from fetching data and not from querying it. If you change "SELECT SomeCol2" to "SELECT SomeCol1", or "SELECT *", or "SELECT SomeCol1, SomeCol2", these perform in the same way:
SELECT * FROM Test WHERE @col IS NULL OR SomeCol = @col
SELECT * FROM Test WHERE SomeCol = @col -- assuming @col IS NOT NULL
If I read it right, that makes more sense to me: MSSQL optimizes fetching if data may come completely from an index.
----------------------------------
António Tavares Lopes