Index Computed Column?

[jim_geissman]

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.

Thanks,
Jim

[Alexander Kuznetsov]

Sure, google up "indexes on computed columns"

[Erland Sommarskog]

(ji**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jim_geissman]

Thanks, Alexander and Erland.

Jim

[jim_geissman]

Oops, I forgot the follow-up....

If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

ji**********@countrywide.com wrote:

Thanks, Alexander and Erland.

Jim

[Erland Sommarskog]

(ji**********@countrywide.com) writes:

Oops, I forgot the follow-up....

If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

No, you should not have to. But you have to ensure that a number
of SET commands are in the right position. Most important, beware of
SET ARITHABORT which must be ON for SQL 2000, but which is never ON
by default, except in Query Analyzer.

-
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Jim,

In addition to Erland's remark, note that the settings during a stored
procedure's execution are the settings in effect during its creation:

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE T1
@C1 CHAR(1), @C2 CHAR(1)
AS
SELECT CASE WHEN @C1 = @C2 THEN 'Inside SP: Equal' ELSE 'Inside SP: Not
Equal' END
GO

SET ANSI_NULLS ON
GO

GO

DECLARE @C1 CHAR(1), @C2 CHAR(1)
SELECT CASE WHEN @C1 = @C2 THEN 'Outside SP: Equal' ELSE 'Outside SP:
Not Equal' END

EXEC T1 @c1, @c2
GO

DROP PROCEDURE T1
GO
---------------------
Outside SP: Not Equal

(1 row(s) affected)
--------------------
Inside SP: Equal

(1 row(s) affected)