Databases Reference
In-Depth Information
… and you can verify that reorganizing an index does not update any statistics:
ALTER INDEX ix_ProductID on dbo
.
SalesOrderDetail REORGANIZE
Listing 3-30.
Finally, for good house-keeping, remove the table you have just created:
DROP
TABLE
dbo
.
SalesOrderDetail
Listing 3-31.
Statistics on Computed Columns
Another interesting step performed during query optimization is the automatic
matching of computed columns. Although computed columns have been available in
previous versions of SQL Server, the automatic matching feature was only introduced
with SQL Server 2005. In this section, I will show you how this feature works, and explain
how computed columns can help to improve the performance of your queries.
A problem faced by some queries using scalar expressions is that they usually cannot
benefit from column statistics and, without statistics, the Query Optimizer will use
the 30% selectivity guess on inequality comparisons, which may produce inefficient
execution plans. A solution to this problem is the use of computed columns, as SQL
Server can automatically create and update statistics on these columns. The great
benefit of this solution is that you don't need to specify the name of the computed
column in your queries for SQL Server to use its statistics. The Query Optimizer
automatically matches the computed column definition to an existing scalar
expression in a query, so your applications do not need to be changed.
