Performance - WHERE condition order

I asked a question on experts-exchange.com and got two different answers. Who is right?
http://www.experts-exchange.com/Mic...er/SQL-Server-2005/Q_23192845.html#discussion
I asked if I run this query:

SQL = "SELECT * FROM tblname WHERE (colname1 = 1) AND (TimeConsumingFunction(colname2) = 5786)"
Would it be faster than this query?:
SQL = "SELECT * FROM tblname WHERE (TimeConsumingFunction(colname2) = 5786) AND (colname1 = 1)"
(note: I only changed the condition order.)

Have you looked at the execution plan generated for both queries.

<<What is the index criteria on this table?>>

Primary key field only (ID)

<<Have you looked at the execution plan generated for both queries.>>

No, I was just thinking this up. I figure, why would SQL Server not check if the first condition is true and if not, go to the next record rather than checking ALL conditions..?

In case anyone comes across this thread, angelIII (of experts-exchange.com) suggested the following subquery:

SQL = "
SELECT *
FROM ( SELECT *
FROM tblname
WHERE colname1 = 1
) subquery
WHERE TimeConsumingFunction(colname2) = 5786
"
If he's right that order means nothing, then this seems to be the best solution to a slow WHERE condition.

Since SQL generally works left-to-right, and can short-circuit where appropriate, this version is better:
WHERE (colname1 = 1) AND (TimeConsumingFunction(colname2) = 5786)
However, note that time is saved *only* if the first condition is *false*. If the first is true, SQL must check the second anyway.
That's why I think angeliii's subquery method should perform worse that the version above as well ... although SQL has been known to produce odd results on occassion [].

Thus, overall, I do think angeliii is wrong about this. And it's easy enough to prove that SQL does short-circuit (i.e. skip unneeded tests) when possible. Run this code:
DECLARE @var1 INT
SET @var1 = 2
WHILE @var1 >= 1
BEGIN
IF @var1 = 1 AND DATEADD(DAY, 1, '19001131') < '20000101'
PRINT 'True'
ELSE
PRINT 'False'
SET @var1 = @var1 - 1
END --WHILE
Note that the first pass, with a value of 2, completes, even though an invalid date was provided to DATEADD -- proof that SQL did not even attempt to run the DATEADD(), since the first statement was false.
In the second pass, the first condition is true, so to resolve the "AND" SQL must evaluate the second expression.
Bottom line: when possible, put the fastest and/or most determining condition(s) first.
It is true that SQL can re-arrange the order of conditions. But it doesn't do it willy-nilly, so you still should check simple conditions in the best order yourself.

ScottPletcher, I can't thank you enough for such informative information! Just what I was looking for.
I wonder why this performance tip is not noted in more places..

Better still - try to find a solution that eliminates the UDF from the WHERE statement. The use of the UDF means indexes are getting ignored.
To force the order of the conditions, the solution with a derived table that vspin posted should work at all times.

subquery doesn't mean forced order evaluation.
USE AdventureWorks
SELECT *
FROM
(
SELECT *
FROM Person.Address
WHERE AddressID / 0 = 1
) AS t1
WHERE StateProvinceID = 78
SELECT *
FROM
(
SELECT *
FROM Person.Address
WHERE StateProvinceID = 78
) AS t1
WHERE AddressID / 0 = 1
if it would then the first query would error out and the second wouldn't. but neither error out
Order means nothing.
You CAN'T force order in where codition evaluation!