Hints can be used with indexed views if you find that the Query Optimizer has not chosen the ideal index for best performance. Normally, if the following conditions are met, the Query Optimizer will automatically consider the use of an indexed view if one exists when running a query:
- SQL Server 2000/2005 Enterprise Edition is used.
- These options are set to on:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIERS
- This option must be set to off:
- NUMERIC_ROUNDABORT
But if you find that the index on the indexed view is not optimal, you can specify which index to use in an indexed view if you do the follow two steps:
- Use the NOEXPAND hint, which tells the Query Optimizer to treat the view like a table with a clustered index, and . . .
- Use the INDEX hint to specify which index to use. The syntax is: INDEX ( index_val [ ,…n ] ) where index_val is the name or index ID of the index in the indexed view.
If you want to force a clustered index scan of the indexed view, use: “INDEX(0)”. [2000, 2005] Updated 5-1-2006
]]>