By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,795 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,795 IT Pros & Developers. It's quick & easy.

Index Computed Column?

P: n/a
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

Jul 28 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Sure, google up "indexes on computed columns"

Jul 28 '06 #2

P: n/a
(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
Jul 28 '06 #3

P: n/a
Thanks, Alexander and Erland.

Jim

Jul 30 '06 #4

P: n/a
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
Jul 30 '06 #5

P: n/a
(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
Jul 30 '06 #6

P: n/a
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)

Jul 31 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.