SQL Server Query Plans: Startup Expression Predicate

I’ve already posted about predicates in query plans, but here is one more: Startup Expression Predicate. Again, it’s better to illustrate its behavior by example. Let’s create a small table with one clustered index and put some data into it.

Now, look at the following query. It should calculate the amount of rows in the table, but only if @return_anything flag is on.

The query is returning about 70 thousand rows and does a certain amount of logical reads.

Table ‘startup_expression_predicate_example’. Scan count 1, logical reads 10042, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 17 ms.

Let’s look at the query plan now.


We see the Filter operator with Startup Expression Predicate before Clustered Index Seek. The SQL Server fires the whole subtree to the left from the Filter operator only when its expression is true. Therefore, if we try to execute the same query with @return_anything equals to zero, it won’t even touch the table.

Execution statistics is quite simple.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

You can encounter this query plan in different situation, don’t be afraid of it, it’s a great optimization that make execution simpler by skipping the whole parts of the query plans if they are not needed.