THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

The Query Optimizer and Computed Columns

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

 

clip_image002

 

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.

 

clip_image004

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

Published Monday, August 10, 2009 12:39 AM by Ben Nevarez
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement