Databases Reference
In-Depth Information
In this case there is no check constraint involved; both predicates are valid and each
will individually return records, but they contradict each other when they are run
together. As a result, the query returns no records and the plan shows a Constant Scan
operator similar to the plan in Figure 5-8. This may just look like a badly written query,
but remember that some predicates may already be included in, for example, view
definitions, and the developer of the query may be unaware of those. For example, in
Listing 5-31, a view may include the predicate
ManagerID > 10
and a developer may
call the view using the predicate
ManagerID < 5
. Since both predicates contradict each
other a Constant Scan operator will be used again instead.
Trivial plan
The optimization process may be expensive to initialize and run for very simple queries
that don't require any cost estimation. To avoid this expensive operation for simple
queries, SQL Server uses the trivial plan optimization. In short, if there's only one way,
or one obvious best way to execute the query, depending on the query definition and
available metadata, a lot of work can be avoided. For example, the following
Adventure-
Works
query will produce a trivial plan:
SELECT
*
FROM
dbo
.
DatabaseLog
Listing 5-32.
The execution plan will show if a trivial plan optimization was performed; the
Optimization Level entry in the Properties window of a graphical plan will show
TRIVIAL, as shown in Figure 5-9. In the same way, an XML plan will show the
StatementOptmLevel
attribute as
TRIVIAL
, as shown in the XML fragment
in Listing 5-33.
