RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

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

n index on a computed column can boost your SQL Server query's performance or even implement a complex business rule. (Computed columns do not store their own values; their values are derived from regular columns.) The following examples describe the scenarios in which this technique would be useful.

Use Computed Columns to Speed Up Search

Consider a very simple situation: even with an index on last_name, the following query will run slowly because its search criteria are not index-friendly:

select last_name, first_name, address from party
where soundex(last_name) = soundex(@last_name) 

If you need to make such searches frequently, need them to respond quickly, or both, add a computed column and create an index on it:

alter table party add soundex_last_name as soundex(last_name)
create index soundex_last_name_ind on party(soundex_last_name)

The query won't run any faster on SQL Server 2000, however, until you explicitly specify the computed column soundex_last_name. That done, the optimizer will use the index that you just created and the query will return much faster:

select last_name, first_name, address from party
where soundex_last_name = soundex(@last_name) 

Note: you can build an index on a computed column only if the column is deterministic and several database settings are set up properly. For more detail, refer to "Creating Indexes on Computed Columns" and "SET Options That Affect Results" on MSDN.

Another common scenario in which an index on a computed column comes in very handy is when your users submit cookie recipes for a cookie recipe contest. Because many users hit the "Submit" button several times, you need to check whether the submitted recipe is already in the database before storing it. Because a recipe may use up to 6Kb, you cannot use an index on the recipe text. Again, by using an index on a computed column:

alter table recipe add text_checksum as checksum(recipe_text)
create index text_checksum_ind on recipe(text_checksum)

You can search for a matching recipe very efficiently:

select submitted_at, recipe_text from recipe 
where text_checksum = checksum('Just buy some saltines')
and recipe_text = 'Just buy some saltines'

Note: the second condition in the where clause is necessary because different recipe texts may have the same checksum.

If you frequently search by specifying how a string ends, such as the following:

last_name like '%stone'

Consider creating a computed column as reverse(last_name) and then creating an index on it. When you do that and rewrite your query as reverse_last_name (like 'enots%'), your searches are likely to get a performance boost.

Note: in many cases, using an index on a computed column is overkill. For instance, consider this query:

select sale_amount, sale_date, checkout_counter 
from sales
— this expression truncates sale_date to midnight, 
where dateadd(day, datediff(day,'20070101', sale_date),'20070101') = 
— @date is already truncated to midnight

If you already have an index on sale_date, do not rush to create a computed column based on the expression used in the query, and do not rush to create an index on the computed column either. A much simpler alternative is to just rewrite the query's search criteria as follows and then utilize the existing index on sale_date:

where @date <= sale_date and sale_date < @next_date

If you don't already have an index on sale_date, consider creating it—you may reuse it in many situations besides speeding up the query you are currently working on.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date