Microsoft SQL Server 2012 Bible

Query Compiling

As previously noted, when SQL Server compiles a statement, it stores the execution plan in the Plan Cache, which stays there, ready to be used again, as long as the plan is useful and there is no memory pressure.

The Query Optimizer

The Query Optimizer is a component of the relational engine that creates execution plans. Think of the Query Optimizer as the brain of the engine because it is responsible for making your queries run as quickly and efficiently as possible. There are several components and stages to compiling a query.

First, the Parser dissects the T-SQL code, ensures that it is valid code, and generates a Parse Tree — a logical representation of the query.

Next, the Algebrizer attempts to simplify any arguments, resolves any object names and aliases, identifies any data type conversions required, and binds any aggregate functions (group by, count(*)). The result is a Query Processor Tree, which is a corrected version of the Parse Tree ready for the Query Optimizer.

Delayed Name Resolution means that SQL Server allows a stored procedure to be created even if the objects it references don't yet exist. The idea is that the object might be created by the time the code is executed. Objects aren't physically checked until the Algebrizer checks for them.

Best Practice

Delayed name resolution is one more reason why a simple parse check is insufficient when developing SQL code. Unit testing against sample data that exercises every use case, including dynamically ...