Databases Reference
In-Depth Information
Unfortunately, for automatic matching to work, the expression must be exactly the same
as the computed column definition. So, if I change the query to
UnitPrice
*
OrderQty
,
instead of
OrderQty
*
UnitPrice
, the execution plan will show an estimated number of
rows of 30% again, as this query will demonstrate:
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
UnitPrice
*
OrderQty
>
10000
Listing 3-36.
Finally, drop the created computed column:
ALTER
TABLE
Sales
.
SalesOrderDetail
DROP
COLUMN
cc
Listing 3-37.
Filtered Statistics
Filtered statistics are statistics created on a subset of records in a table. Filtered statistics
are automatically created when filtered indexes are created, but they can also be created
manually by specifying a
WHERE
clause on the
CREATE
STATISTICS
statement. As you
might imagine, filtered statistics can help on queries accessing specific subsets of data.
They can also be useful in situations like correlated columns, especially when one of
these columns has a small number of unique values, and you can create multiple filtered
statistics for each one of these distinct values. As shown in the histogram section previ-
ously, when using multiple predicates, SQL Server assumes that each clause in a query is
independent and, if the columns used in this query were correlated, then the cardinality
estimation would be incorrect. Filtered statistics may also help on huge tables where a
large number of unique values are not accurately represented in the 200-step limitation
currently enforced on histograms.
