Posted by Erland Sommarskog on 07/28/06 21:31

(jim_geissman@countrywide.com) writes:
> Can I create an index on a variation of a column that isn't actually in
> the table?
>
> I have a ParcelNumber column, with values like
>
> 123 AB-670
> 12345ABC 000-00-040
> 12-345-67
> AP34567890
>
> The blanks and non-alphanumeric characters cause problems with users,
> because sometimes they're there, and sometimes they aren't. So I would
> like to create an index based on this column, with the non-alphanumeric
> characters squeezed out. Of course I can add such a column to the
> table and index it, but I'm wondering if it can be done without
> actually adding the column.

Yes, you can, provided that your computation is deterministic.

First define the computed column:

ALTER TABLE tbl ADD computedcol AS <expression>

Then just create an index on the column. You will be told if the column
is not good for this. Reading the topic on CREATE INDEX is a good idea.

In SQL 2005 you can add PERSISTED after the column definition. This
permits you to persist a computed column without indexing it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]