Databases Reference
In-Depth Information
Note
This topic contains a large number of example SQL queries, all of which are based on the Adventure-
Works database, although Chapter 6 additionally uses the AdventureWorksDW database. All code has
been tested on both SQL Server 2008 and SQL Server 2008 R2. Note that these sample databases are not
included in your SQL Server installation by default, but can be downloaded from the CodePlex website.
You need to download the family of sample databases for your version, either SQL Server 2008 or SQL
Server 2008 R2. During installation, you may choose to install all the databases or, at least, Adventure-
Works and AdventureWorksDW.
How the Query Optimizer Works
At the core of the SQL Server Database Engine are two major components: the
storage
engine
and the
query processor
, also called the relational engine. The storage engine is
responsible for reading data between the disk and memory in a manner that optimizes
concurrency while maintaining data integrity. The query processor, as the name suggests,
accepts all queries submitted to SQL Server, devises a plan for their optimal execution,
and then executes the plan and delivers the required results.
Queries are submitted to SQL Server using the SQL language (or T-SQL, the Microsoft
SQL Server extension to SQL). Since SQL is a high-level declarative language, it only
defines what data to get from the database, not the steps required to retrieve that data, or
any of the algorithms for processing the request. Thus, for each query it receives, the first
job of the query processor is to devise a plan, as quickly as possible, which describes the
best possible way (or, at the very least, an efficient way) to execute said query. Its second
job is to execute the query according to that plan.
Each of these tasks is delegated to a separate component within the query processor;
the
Query Optimizer
devises the plan and then passes it along to the
execution engine,
which will actually execute the plan and get the results from the database.
