[Solved] determine order of operations in query

Question

Yes it is largely possible (though there are some caveats and counter examples discussed in the answers here)

SELECT *
FROM   Foo
WHERE  1 = CASE
             WHEN Name IN ( 'name1', 'name2' ) THEN
               CASE
                 WHEN Type = 1 THEN
                   CASE
                     WHEN ( Date < '2013-01-01'
                            AND Date > '2010-01-01' ) THEN 1
                   END
               END
           END

But why bother? There are only very limited circumstances in which I can see this would be useful (e.g. preventing divide by zero if an earlier predicate evaluated to 0).

Wrapping the predicates up like this makes the query completely unsargable and prevents index usage for any of the three (otherwise sargable) predicates. It guarantees a full scan reading all rows.

To see an example of this

CREATE TABLE Foo
  (
     Id     INT IDENTITY PRIMARY KEY,
     Name   VARCHAR(10),
     [Date] DATE,
     [Type] TINYINT,
     Filler CHAR(8000) NULL
  )

CREATE NONCLUSTERED INDEX IX_Name
  ON Foo(Name)

CREATE NONCLUSTERED INDEX IX_Date
  ON Foo(Date)

CREATE NONCLUSTERED INDEX IX_Type
  ON Foo(Type)

INSERT INTO Foo
            (Name,
             [Date],
             [Type])
SELECT TOP (100000) 'name' + CAST(0 + CRYPT_GEN_RANDOM(1) AS VARCHAR),
                    DATEADD(DAY, 7 * CRYPT_GEN_RANDOM(1), '2012-01-01'),
                    0 + CRYPT_GEN_RANDOM(1)
FROM   master..spt_values v1,
       master..spt_values v2

Then running the original query in the question vs this query gives plans

Note the second query is costed as being 100% of the cost of the batch.

The Query optimizer left to its own devices first seeks into the 414 rows matching the type predicate and uses that as a build input for the hash table. It then seeks into the 728 rows matching the name, sees if it matches anything in the hash table and for the 4 that do it performs a key lookup for the other columns and evaluates the Date predicate against those. Finally it returns the single matching row.

The second query just ploughs through all the rows in the table and evaluates the predicates in the desired order. The difference in number of pages read is pretty significant.

Original Query

Table 'Foo'. Scan count 3, logical reads 23,
Table 'Worktable'. Scan count 0, logical reads 0

Nested case

Table 'Foo'. Scan count 1, logical reads 100373

Accepted Answer