Hints with Indexed Views

By : Brad McGehee Apr 11, 2007

Printer friendly

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

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)