Understanding Indexed Views - SS SLUG Aug 2013 – Demo IV

Here is another post on a demo did at Aug meet-up. This post speaks about Indexed Views.

As the name says, views can be made as Indexed Views by adding indexes on them. If you are not familiar with this concept, a general question comes up; Views are not physical objects, SQL Server holds the definitions of them only, then how indexes are maintained with views? Here is the answer if you have the same questions; Once a clustered index is added to a view, it becomes a physical object, it becomes something similar to a table, it becomes an Indexed View.

What is the point of adding an index to a view? Can we improve the performance of a normal view that contains simple, single-table query? No, we cannot. This is not for such queries. This is typically for views that are formed with multiple tables and contains aggregations. Assume that you have a view that joins 2-3 tables with millions of records and then performs set of aggregations. Aggregations reduce number of records returned but it still needs to read all records, increasing IO operations. If all three tables are maintained with 30,000 pages, whenever the view is accessed, all 30,000 pages have to be read and then aggregations should be done. Making this view as an Indexed view, number of pages to be read is eliminated and aggregation is not required to be performed. This definitely improves the performance.

Here is the demo code shown with the presentation on it.

USE AdventureWorks

GO

-- create a view from Sales header and details

CREATE VIEW dbo.GetSales

AS

SELECT h.SalesOrderID, h.CustomerID, SUM(d.LineTotal) LineTotal  

FROM Sales.SalesOrderHeader h

    INNER JOIN Sales.SalesOrderDetail d

        ON h.SalesOrderID = d.SalesOrderID

GROUP BY h.SalesOrderID, h.CustomerID

GO

-- View data from GetSales view

-- Note the number pages reading during data retrieval

SET STATISTICS IO ON

SELECT * FROM dbo.GetSales

SET STATISTICS IO OFF

Look at the output and the plan;

Now let’s make the view as an Indexed view. There are many rules to be followed, key rules are listed below;

1. All columns in the view must be deterministic.

2. The SCHEMA_BINDING view option must be should when creating.

3. The clustered index must be created as unique.

4. Tables references in the view must be two-part naming.

5. The COUNT_BIG() function must be included if aggregation is used.

6. Some aggregations, such as AVG, MAX, MIN are disallowed in indexed views.

-- Making the view as INDEXED VIEW

ALTER VIEW dbo.GetSales

WITH SCHEMABINDING

AS

SELECT h.SalesOrderID

    , h.CustomerID

    , SUM(d.LineTotal) LineTotal

    , COUNT_BIG(*) CountBig -- this is required

FROM Sales.SalesOrderHeader h

    INNER JOIN Sales.SalesOrderDetail d

        ON h.SalesOrderID = d.SalesOrderID

GROUP BY h.SalesOrderID, h.CustomerID

GO

-- This makes the view as INDEXED VIEW

CREATE UNIQUE CLUSTERED INDEX IX_GetSales ON dbo.GetSales (SalesOrderID, CustomerID)

GO

-- Get data from view and see, note the number of data pages read

-- This shows that view is now physically available and data is loaded from it

SET STATISTICS IO ON

SELECT SalesOrderID, CustomerID, LineTotal FROM dbo.GetSales 

SET STATISTICS IO OFF

Note the pages accessed and the plan;

As you see, performance is significantly improved. However there is something needs to be considered when creating Indexed Views. That is the cost for maintaining it. Every changes make to underline tables require an update on the view. It is transparent but a cost is involved. Therefore implement this for tables that are not frequently updated.

Note that if you are using Standard Edition, you need to use NOEXPAND hint with SELECT statement like below. Otherwise SQL Server will not use the added index.

SELECT SalesOrderID, CustomerID, LineTotal FROM dbo.GetSales WITH (NOEXPAND )