index views ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jerome - 21 Apr 2005 08:47 GMT

Hi,

Is it possible to create some kind of indexes on views
based on a substring column ?

View PROD_NAME:
Select substring (PROD_STRING,10,20) as PROD_NAME, PROD_NO
from PROD_TABLE

View PROD_TYPE:
Select substring (PROD_STRING,5,5) as PROD_TYPE, PROD_NO
from PROD_TABLE

Of course it would be better to have separate columns in
PROD_TABLE but (for me) it is not possible to change or
set triggers for updating alternative tables.

We use SQL2000 st ed SP3.

/Jerome

Reply to this Message

Jacco Schalkwijk - 21 Apr 2005 13:32 GMT

You can create indexed view in SQL Server 2000, but they will only work
seamlessly if you have Enterprise Edition. With Enterprise Edition the Query
Optimizer will look to use indexed views even if the view isn't directly
used in the SQL statement. With Standard Edition, you can create indexed
views, but they will only be used by the Query Optimizer if you access them
directly and use the WITH(NOEXPAND) clause, for example:

SELECT PROD_NAME, PROD_NO FROM PROD_NAME WITH (NOEXPAND)

However, if you can make changes to your tables, you can use a computed
column and index that column:

ALTER TABLE PROD_TABLE ADD substring (PROD_STRING,5,5) as PROD_TYPE,
substring (PROD_STRING,10,20) as PROD_NAME

Signature

Jacco Schalkwijk
SQL Server MVP

> Hi,
>
[quoted text clipped - 16 lines]
>
> /Jerome

Reply to this Message