Thursday, December 13, 2007

Points for Indexed View in TSQL

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)".

No comments: