sql server data warehouse: 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

--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]

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.