Browse DevX
Sign up for e-mail newsletters from DevX


Indexes on Computed Columns: Speed Up Queries, Add Business Rules : Page 3

Using an index on a computed column, you can implement complex business rules or just give your SQL Server queries a performance boost.


Computed Columns in Covering Indexes

If you need to make the following query run as fast as possible:

— in this case, all the sales in the table occurred after 1990,
— so we won't get negative days-of-week
datediff(day,convert(datetime,'19860202',112), sale_date)%7 as sale_day_of_week,
datediff(hour,convert(datetime,'19860202',112), sale_date)%24 as sale_hour, 
from sales
group by datediff(day,convert(datetime,'19860202',112), sale_date)%7,
datediff(hour,convert(datetime,'19860202',112), sale_date)%24

You can create computed columns and an index that will cover the query as follows:

alter table sales add sale_day_of_week as 
  datediff(day,convert(datetime,'19860202',112), sale_date)%7;
alter table sales add sale_hour_of_day as 
  datediff(hour,convert(datetime,'19860202',112), sale_date)%24;
create index sale_by_day_hour on sales(sale_day_of_week, sale_hour_of_day, sale_amount);

That done, rewrite the query using the column names sale_day_of_week and sale_hour_of_day. The query will run much faster. However, do not forget that you have a somewhat slower but way simpler alternative. In many cases, an index on (sale_date, sale_amount) would be good enough but not quite as good as the index sale_by_day_hour. In most circumstances, you would want to keep things simple and go for a regular index on (sale_date, sale_amount).

In some cases, creating an indexed view and materializing the query also might make sense. The query against the then indexed view might run much faster, but the indexed view could introduce severe lock contention (refer to Books Online for more detail).

Use in Moderation

You've seen the benefits of using an index on a computed column. But use the technique in moderation—only after thoroughly considering alternatives, which can be simpler.

Alexander Kuznetsov has over 10 years of experience in database design, development, troubleshooting, and administration. Currently, he works with DRW Trading Group and concentrates on database design, development, and performance improvements.
Thanks for your registration, follow us on our social networks to keep up-to-date