Create an Indexed View

An indexed view is a view whose result set is stored in the database for fast access. Indexed views are supported when your Microsoft Access project is connected to Microsoft SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition, but they are not supported in Microsoft SQL Server 2000 Desktop Edition. Indexed views are best used when data is read-only (such as a decision support system), queries of the indexed view do not involve aggregates or joins, and the base table schema definitions of the indexed view are not likely to change. For more information on indexed views, see the SQL Server documentation.

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. Add tables to the Diagram pane. Because you intend this to be an indexed view, be sure to add only tables that you own.
3. Select which columns you want included in the view. Do not use the asterisk (*); you must explicitly select each column that you want to appear in the indexed view.
4. Right-click the background of the Diagram pane, then choose Manage Indexes from the shortcut menu. The Indexes dialog box appears.

   Note   There are many situations in which the Manage Indexes command is unavailable, because there are many restrictions on which views can be indexed. For example, you cannot index a view unless you are the owner of each table contributing to it. For a complete description of the restrictions on creating indexed views, see the Microsoft SQL Server documentation.

5. Within the dialog box, click New.
6. Supply the information for the index definition — index name, index columns and their order, index file group, and the other index settings. For more information on each dialog box option, click Help.