Indexed Views

When loading data into a decision support system (DSS), you often build summary data that can be drilled into with an OLAP-type application. As business intelligence applications grow, the need to load and interrogate massive amounts of aggregated data also grows. Microsoft has added the Indexed Views feature into SQL Server 2000 to help with this process, and it can be useful in some OLTP situations as well.

Indexed views allow you to materialize views. This means that you can make a view physical rather than virtual. You can place a clustered unique index and many nonclustered indexes on a view. Indexed views are persistent, so as you make a change to the data in the production tables involved in the view, the update also appears in the view's clustered index. Indexed views allow you to quickly compile aggregates of data.

These views, however, should be used sparingly, because they take additional space and can slow down your production tables as data is persisted to the views. Indexed views are only available in SQL Server 2000 Enterprise, Developer, and Evaluation Editions. Other editions allow you to create indexes on views, but SQL Server will not use them, and the view is never materialized.

Tip 

Indexed views can be costly for SQL Server to maintain. You should only use indexed views on tables that are not frequently updated and inserted into.

Indexed Views Requirements and Restrictions

There are many rules that revolve around the creation of indexed views. SQL Server must first guarantee the validity of the data and make sure that it's always consistent. Before creating the view, you must set the following options wherever you're creating the indexed view:

• SET ARITHABORT ON

• SET ANSI_NULLS ON

• SET ANSI_PADDINGS ON

• SET ANSI_WARNINGS ON

• SET CONCAT_NULLS_YIELDS_NULLS ON

• SET NUMERIC_ROUNDABORT ON

• SET QUOTED_IDENTIFIER ON

You would normally create these views in Query Analyzer, and these options can easily be set before creating the index. If you don't set these options, you receive errors like the following (notice that the error states which option is set incorrectly):

Server: Msg 1934, Level 16, State 1, Line 1 CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'.

Other requirements cover those things that can't exist in your view. Some of the common data types or clauses that you must avoid include the following:

• DISTINCT

• text, ntext, or image columns

• ROWSET

• UNION

• Mathematical functions such as count(), min(), max(), avg(), sum(), sum compute(), and compute by()

• Subqueries

• OUTER or SELF joins

• ORDER BY

• TOP

In addition, you can't call other views, and all tables must be in the same database.

The hardest restriction to comply with in views may be the deterministic restriction. You can only create indexed views on columns that are deterministic in the view. If a column is nondeterministic, this means that the value could output two different results based on the same input. The following functions cannot be used in an indexed view:

@@@ERROR	newid()	formatmessage()
host_name()	charindex()	system_user()
@@@IDENTITY	patindex()	getansinull()
ident_incr()	current_timestamp()	textprt()
@@@ROWCOUNT	permissions()	getdate()
ident_seed()	current_user()	textvalid()
@@@TRANCOUNT	session_user()	getutcdate()
identity()	datename()	user_name()
app_name()	stats_date()	host_id()

Some functions may sometimes be nondeterministic, depending on the way you're using them. For example:

• cast() Nondeterministic if used to cast data into a datetime, smalldatetime, or sql_variant

• convert() Nondeterministic if used to cast data into a datetime, smalldatetime, or sql_variant

• checksum() Deterministic except for CHECKSUM(*)

• isdate() Can be deterministic if it you use the convert() function to convert the data to the proper style

• rand() Only deterministic if the seed parameter is specified

Rather than have to remember a list of potential nondeterministic problems, you can run a query that will output 1 if the column is deterministic and 0 if it is not. For example, the following query against the CUST_ORDERS view determines whether the CompanyName column is deterministic:

SELECT (columnproperty(object_id('CUST_ORDERS'),'CompanyName',  'IsDeterministic') 

SQL Server tells you whether the indexed view has any nondeterministic values in it when you try to create the view. When you try to create an indexed view that contains nondeterministic columns, you see the following error:

Server: Msg 1933, Level 16, State 1, Line 1 Cannot create index because the key column 'SubTotal' is nondeterministic or imprecise.

Creating an Indexed View

I'll create an indexed view on a few tables in the Northwind database. This simple example demonstrates a way to determine which customers have placed the most orders over the life of the ordering database. To create the indexed view, use the following syntax:

CREATE VIEW CUST_ORDERS WITH SCHEMABINDING AS SELECT     dbo.Customers.CompanyName, dbo.Customers.CustomerID, dbo.Orders.OrderID, dbo.Orders.OrderDate FROM       dbo.Orders INNER JOIN            dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

As you can see, this looks like you're creating a normal view except for the WITH SCHEMABINDING clause. Schema binding freezes schema changes to prevent the view from becoming 'orphaned.'

Normally, if you create a view and then drop a table the view needs, SQL Server won't return an error until you try to select from the view. Schema binding prevents this from occurring and is required for the indexed view.

Once the schema is bound, you cannot modify any of the tables that participate in the view. If you try to modify or drop any of the tables, you receive the following error in Enterprise Manager.

'Order Details' table - Error validating check constraint 'CK_Discount'. - Warning: The following schema-bound objects will be modified: View 'dbo.Order_Subtotals': schema binding will be removed. 

To modify the schema, you must drop the view, and then re-create it after you've made the necessary changes to the schema. In the CREATE VIEW syntax I just used, note that all references to tables use the two-part name (owner.tablename). You cannot reference any tables outside the database. A common mistake is using only the table's name in the view. If you do this, you see the following error:

Server: Msg 4512, Level 16, State 3, Procedure Order_Subtotals, Line 4 Cannot schema bind view 'Order_Subtotals' because name 'Order Details' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Caution 

With indexed views, you can't use SELECT * or SELECT *.Tablename to select every column from a table.

The view you create is no different from any other view on the server at this stage. However, if you issue an sp_spaceused system stored procedure, which tells you how much space a SQL Server object is using, it returns an error. You can test this by running the following statement:

sp_spaceused CUST_ORDERS

which returns the following error:

Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

This error means that the view has not materialized yet. It will not materialize until you place the first unique clustered index on the view. You can place one unique clustered index on the view followed by nonclustered indexes. To create the index, use the following syntax:

CREATE UNIQUE CLUSTERED INDEX CUST_ORD_IDX on CUST_ORDERS(ORDERID) CREATE NONCLUSTERED INDEX CUST_CUSTID_IDX ON CUST_ORDERS(CUSTOMERID)

Note 

You must be the view's owner to place an index on it.

You can determine whether the view is indexed by using the objectproperty() function as shown here:

SELECT objectproperty(object_id('CUST_ORDERS'), 'IsIndexed')

A result of 1 means an indexed view.

In the Trenches

You may run into a compatibility problem if your database is set to a compatibility level other than 80. In that case, you receive the following error:

Server: Msg 1959, Level 16, State 1, Line 1 Cannot create index on view or computed column because this database is not SQL Server compatible.

To fix the problem, change the compatibility level for your database to 80.

Now, if you issue an sp_spaceused against the view, you see results. This means that the table has been materialized and is physical.

name         rows  reserved  data    index_size  unused ------------ ----- --------- ------- ----------- -------  CUST_ORDERS  830   64 KB     64 KB   16 KB       -16 KB

Querying Indexed Views

You query an indexed view the same way you query any other view. The only difference is that the workload on SQL Server is much lower. You begin to see the benefits of an index view when you work with aggregates. For example, the following complex query is designed to find out how many orders have been placed by an individual client:

SELECT CUSTOMERID, Count(OrderID) as "Total Orders" FROM CUST_ORDERS GROUP BY CustomerID

The query outputs the following results:

CUSTOMERID   Total Orders  ----------   ------------  ALFKI        6 ANATR        4 ANTON        7 AROUT       13 BERGS       18

Using an indexed view, this query runs in half the time it would take without an indexed view.