THE SQL Server Blog Spot on the Web

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

Alexander Kuznetsov

When correlated columns are in different tables

In my previous post

I described how correlation between columns may confuse the optimizer and cause it to choose an inefficient plan. I also recommended index covering as the most robust way to optimize queries that involve correlated columns.

However, there are quite a few cases when correlated columns are in different tables. For example,  employees' home zipcodes and their cell phone area codes may be strongly correlated. Similarly, car models and kinds of repair work for those cars may be strongly correlated too.

In such cases indexed views come very handy, because they can allow the optimizer to satisfy such queries with one and the same plan, the range scan, regardless of the expected cardinality. Even if the optimizer's estimated cardinality is wildly wrong, it does not matter if the execution plan is still the same range scan.

Of course, indexed views come with a rather hefty price tag,

and in some cases you may have to drop them altogether.


Published Tuesday, June 9, 2009 10:21 PM by Alexander Kuznetsov


No Comments
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement