Hints with Indexed Views

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




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |