Quick steps: Creating an indexed view

• Published: 22 Jan 2006

The following excerpt, courtesy of Que Certification, is from Chapter 10 of the book MCAD/MCSD/MCSE Training Guide (70-229): SQL Server 2000 Database Design and Implementation by Thomas Moore and Ed Tittel. Click for the complete book excerpt series or purchase the book.


 

STEP BY STEP - 10.9

Creating an indexed view

1. Open the Query Analyzer by selecting Query Analyzer from the Start menu.

2. Type in the following in the code pane. The first part of the code configures session-level settings. Remember, some settings need to be enabled and disabled before you can create an indexed view. The second part creates a view so that you can create an index. The last part creates a unique clustered index on the view as shown in Figure 10.8. Notice that WITH SCHEMABINDING option has to be specified.

Use Pubs
SET ANSI_PADDING,CONCAT_NULL_YIELDS_NULL,
  ANSI_WARNINGS,ARITHABORT,ANSI_NULLS,
  QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

GO

CREATE   VIEW IndexView WITH SCHEMABINDING
AS
SELECT Au_Fname + ' ' + Au_Lname, Au_id
FROM Authors
WHERE Contract = 1

GO

CREATE UNIQUE CLUSTERED Index MyINDEX ON IndexView
  (au_id)
Go

3. To see your index view, open the Query Analyzer and the Object browser.

4. Expand Pubs and then expand views.
Creating an indexed view

5. Click IndexView and then expand indexes. You should see the index you created listed under Indexes as shown in Figure 10.9.

Creating an indexed view

All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Click for the complete book excerpt series.