Zurück

Materialized Views in SQL Server 2000

Overview

SQL Server 2000 Indexed Views are similar to Materialized Views in Oracle - the Result Set is stored in the Database. Query Performance can be dramatically enhanced using Indexed Views. Create an Indexed View by implementing a UNIQUE CLUSTERED index on the view. The results of the view are stored in the leaf-level pages of the clustered index.

An Indexed View automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server 2000 can find the rows in the index that are affected by any data modification.

Guidelines for Creating Indexed Views

The SQL Server 2000 Query Optimizer automatically determines whether a given query will benefit from using an Index View.

Create Indexed Views when:

  • The performance gain of improved speed in retrieving results outweighs the increased maintenance cost.

  • The underlying data is infrequently updated.

  • Queries perform a significant amount of joins and aggregations that either process many rows or are performed frequently by many users.

Restrictions on Creating Indexed Views

Consider the following guidelines:

  • The first index that you create on the view must be a UNIQUE CLUSTRERD index

  • You must create the view with the SCHEMABINDING option.

  • The view can reference base tables, but it cannot reference other views.

  • You must use two-part names to reference tables.

Example

1).  Note the Execution Plan of the following query

SET SHOWPLAN_ALL ON

SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev,
       OrderDate, ProductID
  FROM dbo.[Order Details] od, dbo.Orders o
 WHERE od.OrderID=o.OrderID AND ProductID in (2,4,25)
   AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC

|--Sort(ORDER BY)
  |--Stream Aggregate(GROUP BY) ...
     |--Sort(ORDER BY) ...
        |--Nested Loops(Inner Join) ...
           |--Index Seek ...
           |--Clustered Index Seek ...

2). Create the Indexed View

USE Northwind
GO

SET SHOWPLAN_ALL OFF
GO

-- Set the options to support Index Views
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON
GO
SET ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

-- Create the Index View
DROP VIEW VW_summary
CREATE VIEW VW_summary
WITH SCHEMABINDING
AS
   SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev,
          OrderDate, ProductID, COUNT_BIG(*) AS COUNT
     FROM dbo.[Order Details] od, dbo.Orders o
    WHERE od.OrderID=o.OrderID
   GROUP BY OrderDate, ProductID
GO

-- Create UNIQUE CLUSTERED index on the view
CREATE UNIQUE CLUSTERED INDEX IVW_summary
  ON VW_summary (OrderDate, ProductID)
GO

3). Check the Execution Plan again on the same Query as in 1).

|--Sort(ORDER BY:([VW_summary] ...
  |--Clustered Index Seek ...

If you prefer a graphical representation of the execution plan, use Query Analyzer.