Builder
http://builder.com.com

Presents your 
SQL SERVER E-NEWSLETTER for July 8, 2003

<------------------------------------------->

SPEED UP PERFORMANCE BY USING AN INDEXED VIEW

Creating a unique clustered index on a view can drastically improve your
view performance because the view becomes stored in the database similar
to how a table is stored.

A standard view is built dynamically, and therefore, the overhead can be
enormous. With an indexed view, the results are stored, and what you
lose in data storage, you gain in performance.

The first index created on a view must be a unique clustered index. In
addition, the creator of the index must also own the tables. These tables,
as well as the view and index itself, must be created in the same database.

Versions prior to SQL Server 2000 only allowed you to create indexes on
tables; SQL Server 2000, however, has added the ability to create indexes
on views. SQL Server 2000 does this by storing the computed query
results and, when data in the view is changed, the index is changed. 
(The results of standard views aren't stored.)

Views have other restrictions, which include the following:

* You cannot have any other views, row set functions, inline functions,
or derived tables within the SELECT statement defining the indexed view.
You can't have the keywords TOP, DISTINCT, COMPUTE, HAVING, and/or 
UNION
in the SELECT statement defining the indexed view. Also, the SELECT
statement can't have a subquery.

* The SELECT list can't have asterisks (*) or other wildcards. It cannot
have DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the
base table or scalar aggregates.

* No OUTER JOIN operations are allowed in any joined tables.

* No subqueries or CONTAINS or FREETEXT predicates are allowed in the
search conditions.

* If the view definition contains a GROUP BY clause, all grouping
columns must appear in the view's SELECT list. These columns also must be
the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.

In addition, here are several system settings you must set in order to
use indexed views:

* NUMERIC_ROUNDABORT must be set to On. This allows the view to round
the result to the precision of the column or storing variable.

* ANSI_PADDING controls the way columns stores values shorter than the
defined size of the column.

* ANSI_WARNINGS specifies SQL-92 standard behavior for several error
conditions.

* CONCAT_NULL_YIELDS_NULL controls whether concatenation results are
treated as null or empty string values.

* ARITHABORT terminates a query when an overflow or divide-by-zero 
error occurs during query execution.

* QUOTED_IDENTIFIER causes SQL Server to follow the SQL-92 rules
regarding quotation mark delimiting identifiers and literal strings.

The example below will create a view, then an index on that view, and
finally a query using the indexed view. But first, we have to adjust 
all the system settings required to allow indexed views to work.

USE Northwind
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

Next, we'll create a view called V1 that creates the columns Revenue,
OrderDate, and ProductID.

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

Next, we'll create the unique clustered index on V1.

CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO

Finally, we'll create a query that uses the indexed view instead of the
dynamic view.

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, 13, 7, 89, 22, 34)
   AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC

As you can see, indexed views aren't easy, but the performance benefits
outweigh the cost in set-up time.

J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------