Can a query use an indexed view in SQL Server Standard Edition?
The optimizer can automatically use indexed views to enhance performance. Multiple sources have incorrectly stated that indexed views can only be used in SQL Server 2000 Enterprise and Developer Editions. Two SQL Server Books Online (BOL) topics—Using Indexes on Views and Resolving Indexes on Views—provide some additional insight into this matter. All versions of SQL Server 2000 let you create an index on a view. However, the Enterprise and Developer Editions will automatically consider the use of an indexed view even if the query doesn't directly mention that view. Here's how it works. Imagine that you have a table called TableA and a view of TableA called IndexedView1 that has multiple indexes created on it. The optimizer in Enterprise Edition will consider using IndexedView1 even if the query references only TableA. Standard Edition can't do that automatically; however, you can let SQL Server use an index on a view by using the noexpand hint against the view like this:
SELECT * FROM IndexedView1 WITH (noexpand)
When you use noexpand, SQL Server can choose which index to use if indexes exist on the view. You can force SQL Server to use a particular index by also specifying the index clause in the query:
SELECT * FROM IndexedView1 WITH (noexpand, index = Index1)
Enterprise Edition makes the process simpler because you don't need to reference the view for SQL Server to consider the index. But yes, it's possible to use an indexed view in Standard Edition.
