Order of execution

Or “Which where runs when?”

There seems to be a lot of misunderstanding about what order conditions within a where clause are executed. In two days I heard from three different places comments about what order conditions must be put in the where clause.

The simple truth is that is doesn’t matter. Let’s look at a very simple example. (Sample code at the end as always)

SELECT * from tblTestWhereOrder WHERE A=1 and B=2 and C is not null

When run, the execution plan shows a table scan with a single filter, for A=1, B=2 and C not null. If the order of filters is changed in the query and the query rerun, the plan shows the same thing. A single filter with all three conditions evaluated.

Now, let’s try adding an index on A. Now the query is run as an index seek on A (essentially doing the filter on A first) and then doing the other two filters later. This is regardless of the order the filters are placed in the where clause.

Now add another index on B. The query is still run as an index seek, using the index on A.

Essentially, the order that filters are applied during the execution of the query has everything to do with the indexes available and on the distribution of data in the columns been searched, and nothing on the order in which they are specified.

Sample code:

create table tblTestWhereOrder (
A int identity,
B int,
C char(1)
)

with Populate (a,b,c) as (
select 1 as A, floor(rand()*25) as B,
CASE floor(rand()*25) WHEN 0 then null else char(65+floor(rand()*25)) end as C
union all
select a+1, floor(rand(A*10000)*25) as B,
CASE floor(rand(a*853200)*25)
WHEN 0 then null
ELSE char(65+floor(rand(a*452059)*25))
END from Populate
where A<1000
)
insert into tblTestWhereOrder (b,c)
select b,c from populate
option (maxrecursion 1000)

create index idx_A on tblTestWhereOrder (A)

create index idx_B on tblTestWhereOrder (B)