Query Optimization and Execution - Pro SQL Server Internals - page 451

During the simplification stage, SQL Server transforms the query tree in a way that simplifies the optimization
process further. Query Optimizer removes contradictions in the queries, performs computed column-matching, and
works with joins, picking an initial join order based on the statistics and cardinality data.
Listing 25-1 provides an example of removing contradicting parts in a query. Both tables, NegativeNumbers and
PositiveNumbers , have CHECK constraints that dictate the domain scope for the values. SQL Server can detect domain
values contradictions, and it understands that an inner join operation will not return any data. It generates the
execution plan, which does not access tables at all, as shown in Figure 25-3 .
Listing 25-1. Removing contradicting parts from the execution plan
create table dbo.PositiveNumbers
(
PositiveNumber int not null,
constraint CHK_PositiveNumbers
check (PositiveNumber > 0)
);
create table dbo.NegativeNumbers
(
NegativeNumber int not null,
constraint CHK_NegativeNumbers
check (NegativeNumber < 0)
);
select *
from dbo.PositiveNumbers e join dbo.NegativeNumbers o on
e.PositiveNumber = o.NegativeNumber
Figure 25-3. Execution plan for the query
After the simplification phase is completed, Query Optimizer checks if there is a trivial plan available for the
query. This happens when a query has either only one plan available to execute, or when the choice of plan is obvious.
Listing 25-2 shows such an example.
Listing 25-2. Query with trivial execution plan
create table dbo.Data
(
ID int not null,
Col1 int not null,
Col2 int not null,
constraint PK_Data
primary key clustered(ID)
);
select * from dbo.Data where ID = 11111;