"SELECT" Statement

I will take you through two steps in which an SELECT statement will get processed...

Optimizing a Select

1. A SELECT statement is non-procedural.
2. A database server needs to optimize a SELECT statement because it is non-procedural (we cannot know   the exact number of steps that the server would undergo to execute the query).
3. Query optimizer does this optimizing query.
4. Tables, views definitions, indexes and statistics are input to the query optimizer and execution plan is its      output.

A query execution plan is a definition of following:

1. Sequence in which the source tables are accessed.

• Usually accessed in any way (top to bottom or bottom to top) and it does not matter.

2. The methods used to extract data from each table.

• Table scan
• Index scan
• Index seek

SQL Server Query optimizer:

1. SQL server query optimizer is cost-based.
2. It selects a plan which has reasonable cost and returns results faster.
3. Optimizer relies on statistics which helps in estimating the costs of the resources required to process a query.

Processing a SELECT statement

1. Parser scans the SELECT statement and breaks it into logical units such as keywords, expressions,    operators and identifiers.

2. A query tree or a sequence tree is built describing the logical steps needed to transform the source data  into result set format.

3. The optimizer analyzes different ways the source tables can be accessed.

4. Then the query tree is updated with the exact series of the steps which gives fast results while consuming  less resources.

5. The final optimized version of query tree is the execution plan.

6. The relational engine starts executing the execution plan upon getting the data from the storage engine.

7. The relational engine itself processes the data returned from the storage engine into the result set format and  sends to client.

Thanks for reading...!
Comments and issues are accepted with great respect.