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.
- The second step is to Resolve objects (Tables, Views, Columns, etc.) to see if they exist. Now, I needed an ‘R’ word to spell the word PROCESS, but this step is technically called the Binding phase because it binds aliases to column or tables. It will also check data types and perform implicit data type conversions if necessary. Both the first and second step are where you will find syntax errors. In most cases, these errors are caused by misspellings or putting commas in the wrong place.
- 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 the fastest steps 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 is 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 will Execute and hopefully return the desired SQL Sets. This is where Run-Time errors will occur that need to be handled using Exception Handling.
Additional submissions of the query, such as from a Stored Procedure, will check the Procedure Cache for existing or similar Execution Plans that could be used for the query. If this is the case, the existing Execution Plan will be used to retrieve your SQL Sets.