This post will describe how a SQL Server will PROCESS a query. The first time through running an Ad Hoc query or Stored Procedure, SQL Server will go through each of the following steps.

The first step is to Parse the statement into keywords, expressions, and operators. This is where the syntax of your query statement is checked for accuracy. Both the first and second steps are where you will find syntax errors. In most cases, these errors are caused by misspellings or putting commas in the wrong place.

The second step is officially known as the binding phase, however, I like to refer to it as the Resolve phase, because it is resolving a variety of items.

• Resolve if a cached plan already exists.
• Resolve object names (tables, columns, etc.) to see if they exist.
• Resolve aliases of columns and tables
• Resolve data types and if implicit data type conversions are needed.

The third step is to Optimize the query. This is where the query optimizer will find different ways of locating data from your tables based on available indexes and/or statistics. Once it figures a good enough plan to retrieve your result set using the least amount of resources, it will create an Execution Plan. Errors would only occur during this stage if there were a lack of hardware resources. Most compile errors happen at the next step. 

The fourth step is to Compile the Execution Plan and store it in the Procedure Cache for future use. 

Finally, the Execution Plan is passed to the Storage Engine to Execute the query and hopefully return the desired SQL Sets. This is where Run-Time errors will occur that need to be managed by using Exception Handling. (SQL Sets are normally called recordsets or rowsets, but I’m making a blog post on the PROCESS of queries not the PBOCERS of queries).

Additional submissions of the query from a Stored Procedure, will check the Procedure Cache for existing or similar Execution Plans that could be re-used for the query. If this is the case, the existing Execution Plan will be used to retrieve the SQL Sets using the Execution Context of the Stored Procedure.

It is possible for an Ad Hoc query to also re-use an existing plan from the procedure cache if the only thing that has changed in the query is the parameter value. Example: If a query had been written WHERE ProductID = 732 and was re-written WHERE ProductID = 738 the execution plan could be re-used, but if anything else changed, even by adding a space, the optimizer would compile a new execution plan.

One final thing. You will notice that you can have both an Estimated Execution Plan and an Actual Execution Plan. The difference is the first will show you what the plan would look like before the plan is compiled and the second will additionally add the values of actually running the plan that was placed in the procedure cache and executed.