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 database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin's blog is at http://www.benjaminnevarez.com and can 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