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

WEBINAR:

On-Demand

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:

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.