Databases Reference
In-Depth Information
DECLARE
@ProductID
int
SET
@ProductID
=
921
SELECT
ProductID
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@ProductID
Listing 3-9.
In this case, the Query Optimizer does not know the value of the
@ProductID
local
variable at optimization time, so it is not able to use the histogram (which we'll discuss
shortly) and will use the density information instead. The estimated number of rows is
obtained using the density multiplied by the number of records in the table which, in our
example, is 0.003759399 * 121317, or 456.079 as shown in Figure 3-2.
Figure 3-2:
Cardinality estimation example using a local variable.
Actually, since the Query Optimizer does not know the value of
@ProductID
at
optimization time, the value of 921 in Listing 3-9 does not matter; any other value will
give exactly the same estimated number of rows and execution plan. Finally, run this
query with an inequality operator:
DECLARE
@pid
int
=
897
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
<
@pid
Listing 3-10.
