Browse DevX
Sign up for e-mail newsletters from DevX


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

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


Use Computed Columns to Implement Complex Business Rules

Suppose you need to store a list of employees, their phone numbers, their e-mail addresses, etc. Some employees have VPN tokens, others don't. You need to make sure that the VPN token numbers entered are unique, while allowing any number of null VPN token numbers. A unique index on VPN token number will not do it, as it won't let you enter more than one null. Use an approach that Ivan Arjentinski and Steve Kass (who also coined the word nullbuster) simultaneously published on the MS SQL Server newsgroup. Assuming that the table employee has a primary key employee_PK, implementing the following business rule is very easy:

alter table employee add nullbuster as (case when VPN_token is null then employee_PK else null end);
create unique index VPN_Unique on employee (VPN_Token, nullbuster);

Similarly, if you have the following table for outstanding tasks:

create table task(
task_ID int not null primary key check(Task_ID > 0),
agent_ID int not null check(agent_ID > 0),
status varchar(10) not null check (status in('Active', 'Pending', 'Completed'))

And you want to implement a business rule "Every agent has at most one active task at any time," use an index on computed columns to implement it:

alter table task add agent_ID_when_active as case when status='Active' then agent_ID else null end;
alter table task add task_ID_when_inactive as case when status='Active' then null else task_ID end;
create unique index one_active_task  on task(agent_ID_when_active, task_ID_when_inactive)

In this particular case, when both agent_ID and task_ID are positive integers, a more efficient solution is using only one computed column and a smaller index on only that column:

alter table task add agent_or_minus_task_ID as case when status='Active' then agent_ID else (0-task_ID) end
create unique index one_active_task  on task(agent_or_minus_task_ID)

Whenever you use approaches like this, consider a traditional alternative: implementing the business rule in either a stored procedure or a trigger. However, implementing such a business rule via an index on a computed column has one very clear advantage: there is no way around it. One can create another stored procedure with different logic, but the rule implemented by an index cannot be bypassed, as long as the index exists.

Note: in some cases, you might want to include a computed column in a primary key or in a unique constraint, which is beyond the scope of this article.

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