I created an indexed view in SQL 2000, and I expected to see the index created on the view referenced in the execution plan when I query the view. Instead, I see the index for the base table referenced in the execution plan. Why?

There are 6,000,000+ records in the base table, and the view only references 256 of these rows.

Here is some of the DDL if you need it:

CREATE TABLE [alarm_t] (
[ct_dtm] [datetime] NOT NULL ,
[dst_flg] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[stn_nm] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[alarm_txt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[utc_dtm] [datetime] NOT NULL ,
[create_utc_dtm] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [alarm_idx2] ON [dbo].[alarm_t]([ct_dtm], [stn_nm], [dst_flg]) ON [PRIMARY]
GO

create view dbo.alarm_Mapbd_v with schemabinding
as
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE [stn_nm] = 'Mapbd'
GO

create unique clustered index alarm_Mapbd_idx1 on dbo.alarm_Mapbd_v
( stn_nm, ct_dtm, dst_flg )
go

update statistics alarm_t
go
update statistics alarm_Mapbd_v
go

The following 2 queries have the exact same execution plan, both showing a cost of 50%. I expected to see the index created on the view referenced in the execution plan for the first query. Is the index created on the view being used?

select stn_nm, ct_dtm, dst_flg
from alarm_Mapbd_v
go
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE [stn_nm] = 'Mapbd'
go

Thanks for your assistance.

Tom

I found the answer. Unfortunately, it is option 1 below. I am using the standard edition of SQL Server.

Q. Why isn't my indexed view being picked up by the query optimizer for use in the query plan?
A. There are three primary reasons the indexed view may not be being chosen by the optimizer:
(1) You are using a version other than Enterprise or Developer edition of SQL Server. Only Enterprise and Developer editions support automatic query-to-indexed-view matching. Reference the indexed view by name and include the NOEXPAND hint to have the query processor use the indexed view in all other editions.
(2) The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn't reference the view. If they are close, this may give you confidence that the decision of whether or not to use the indexed view doesn't matter.
(3) The query optimizer is not matching the query to the indexed view. Double-check the definition of the view and the definition of the query to make sure that a structural match between the two is possible. CASTS, converts, and other expressions that don't logically alter your query result may prevent a match. Also, there are limits to the expression normalization and equivalence and subsumption testing that SQL Server performs. It may not be able to show that some equivalent expressions are the same, or that one expression that is logically subsumed by the other is really subsumed, so it may miss a match.