In my previous post I talked about contradiction detection as one of the early steps in query optimization. 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 introduced until SQL Server 2005. In this post I will show you how this feature works and explain how computed columns can help you improve the performance of your queries.
A problem faced by queries using scalar expressions is that they usually can not take benefit of column statistics. Statistics provide cardinality estimation to help the Query Optimizer to create better execution plans. Without statistics, the Query Optimizer will use a 30% selectivity 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 do not 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.
Indexes can also be created on computed columns but be aware of the requirements described on the Books Online entry ‘Creating Indexes on Computed Columns’.
I will show you one example; run this query against the AdventureWorks database
select * from Sales.SalesOrderDetail
where OrderQty * UnitPrice > 10000
The estimated number of rows is 36,395.1, which is 30% of the total number of rows, 121,317. But the query only returns 772 records.
Now create a computed column
alter table Sales.SalesOrderDetail
add cc as OrderQty * UnitPrice
Run the previous query again. Note that this time the estimated number of rows has changed and it is close to the actual number of rows returned by the query. Replace the 10000 with some other values like 10, 100, 1000 and 5000 and compare the actual and the estimated number of rows.
Note that creating the computed column does not create statistics. These statistics are created only until the query is executed. You can run this to see information about its statistics, which name starts with _WA_Sys_.
select * from sys.stats
where object_id = object_id('Sales.SalesOrderDetail')
Also note that the auto_created field is 1 which means that the statistics were automatically created by SQL Server. Use the following command to display these statistics
dbcc show_statistics ('Sales.SalesOrderDetail', _WA_Sys_00000013_2645B050)
Unfortunately, for automatic matching to work the expression must be exactly the same, if I change the query to UnitPrice * OrderQty, instead of OrderQty * UnitPrice, it will show an estimated number of rows of 30% again.
select * from Sales.SalesOrderDetail
where UnitPrice * OrderQty > 10000
Finally, drop the created computed column
alter table Sales.SalesOrderDetail
drop column cc