Indexed Views

for RuBoard

Typically, a view is a conceptual table only-it does not actually store any of the data it returns. It's a virtual tableyou query it, and, behind the scenes, SQL Server runs its SELECT statement and returns the results. This is the way views have always worked on SQL Server.

Indexed views change this. By indexing a view you can materialize its result set permanently so that querying it in the future is much faster. Given a table with a large number of rows and a view over it that only returns a few of them, the difference an index over the view could make in execution time may well be dramatic.

As with partitioned views, there are a number of restrictions on the types of views that may be indexed. You can read up on these in the Books Online. To easily check whether you can create an index on an existing view, use the IsIndexable property of the OBJECTPROPERTY() function, as I mentioned earlier in the chapter. Be aware that IsIndexable can take a while to return because of all the criteria it must check to determine whether an object can be indexed. The purpose of this section is to discuss general design considerations when working with indexed views.

Indexed View Usage by the Optimizer

That the optimizer may use an index that was built over a view when you query the view should be obvious. As with indexes on tables, this is the whole point of having the index in the first place. However, the SQL Server optimizer can also use indexed views when you don't refer to them directlywhen you query their underlying tables. Take, for example, this view and index (Listing 9-26):

Listing 9-26 A basic indexed view.

 USE pubs GO DROP VIEW dbo.SalesByMonth GO CREATE VIEW dbo.SalesByMonth WITH SCHEMABINDING AS SELECT LEFT(CONVERT(char(8),ord_date,112),6) AS SalesMonth,       COUNT_BIG(*) AS TotalNumSales FROM dbo.sales GROUP BY LEFT(CONVERT(char(8),ord_date,112),6) GO CREATE UNIQUE CLUSTERED INDEX MonthlySales ON dbo.SalesByMonth (SalesMonth) 

A query over the sales table that aggregates on the year and month, as the view does, may indeed benefit from the view's index. For example, consider what happens when we run a query that's very similar to the one that composes the view (Listing 9-27):

Listing 9-27 SQL Server can use view indexes even when not querying the view.

 SELECT  LEFT(CONVERT(char(8),ord_date,112),6) AS SalesMonth,        COUNT(*) AS TotalNumSales FROM dbo.sales s JOIN dbo.titles t ON (s.title_id=s.title_id) GROUP BY LEFT(CONVERT(char(8),ord_date,112),6) 

On the Enterprise Edition of SQL Server, the query plan looks like this:

 Compute Scalar(DEFINE:([Expr1003]=Convert([SalesByMonth].[TotalNumSales]))) --Clustered Index Scan(OBJECT:([pubs].[dbo].[SalesByMonth].[MonthlySales])) 

The index we built over the view is being used to service a query on its base table.

Using Indexed Views on Other Editions of SQL Server

Normally, you can't create or use indexed views on versions of SQL Server other than the Enterprise Edition (EE) and the Developer Edition (DE), but there is a way around this. To "create" an indexed view on the Personal, Standard, or MSDE editions of SQL Server, first create the indexed view on SQL Server EE or DE, then back up the database and load it onto your target server. This will get the object onto your server, but it won't cause the optimizer to use it. To do that, use the NOEXPAND query hint. You can specify the NOEXPAND hint with an indexed view to force the optimizer to consider the indexes on the view. This works on any version of SQL Server. In conjunction with the INDEX hint, you can force the use of a view index, regardless of the SQL Server edition. Listing 9-28 presents an example of the use of the NOEXPAND hint:

Listing 9-28 You can force the optimizer to consider your view indexes via the NOEXPAND hint.

 SELECT  SalesMonth,        TotalNumSales FROM dbo.SalesByMonth (NOEXPAND) 

for RuBoard