Indexed View in MSSQL SERVER:

What is indexed view?

  An indexed view is a view that has a unique clustered index created on it. 

   Normally views do not exist on disk as rows. That changes for indexed views, which exist in the database as rows that realize the view. There can also be non-clustered indexes on the view so long as it has the unique clustered index.

Since the indexed view exists on disk there are two types of overhead added to the database:

• The disk space taken up by the view
• The costs of maintaining the view on disk as the base tables are modified.

Indexed view is helpful in situation where there is high ratio of data retrieval operations to data modifications operation. In that case it will give substantial savings in response time to corresponding queries.

  As all types of view depend on underlying table so is indexed view, as with any other views, indexed views depend on base tables for their data. Such dependency means that if you change a base table contributing to an indexed view, the indexed view might become invalid e.g.  renaming a column that contributes to a view invalidates the view.

     To prevent such problems, SQL Server supports creating views with "schema binding".  Schema binding prohibits any table or column modification that would invalidate the view. SQL Server requires that indexed views have schema binding.

What are the requirements for Indexed views?

There are several requirements that you must take into consideration when using Indexed views.

1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
1. TOP
2. Text, ntext or image columns
3. DISTINCT
4. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
5. SUM on a nullable expression
6. A derived table
7. Rowset function
8. Another view
9. UNION
10. Sub-queries, outer joins, self joins
11. Full-text predicates like CONTAIN or FREETEXT
12. COMPUTE or COMPUTE BY
13. Cannot include order by in view definition

Session Option Requirement:

Following session options must be set when you create an index view

a)      Session Options that must be on

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YEILDS_NULL

QUOTED_IDENTIFIERS

b)     Session options that must be off

NUMERIC_ROUNDABORT

Note: Nondeterministic functions are those who may return different results each time they are called with a specific set of input values even if the database state that they access remains the same e.g. GetDate(), rand(),CURRENT_TIMESTAMP() etc.

Selecting Columns for the Index

  There are limitations on which columns from the view can be in the index. The column expressions must be deterministic and may not have floating-point data. The view can have floating point columns (real or float), it is just that no floating point columns can be in the index.

There are three properties(listed below) returned by the SQL Server built-in function COLUMNPROPERTY that tell you if columns in a view qualifies to be included in an index.

• IsIndexable - Gives the answer: can the column be indexed.
• IsDeterminstic - Non-deterministic columns cannot be indexed.
• IsPrecise - 1 when the column is not a floating point type.

e.g.

SELECT

COLUMNPROPERTY (OBJECT_ID (‘YourTableName’),'YourColumnName',' IsDeterministic')

This return ‘1’, if true ‘0’, if false and null on error.

Ideally Following Steps to be followed while creating Indexed View

How do I create an Indexed View?

1. Make sure that session properties are properly set.
2. Create a deterministic view with new SCHEMABINDING syntax.
3. Create unique Clustered Index.
4. Optionally, create additional nonclustered indexes.

Let’s Try to Create  a indexed view

-- Use the northwind database

USE NORTHWIND

GO

-- Make sure that all of the session settings are set properly

IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

Go

-- Create the view, it must comply with the rules (deterministic)

CREATE VIEW PRODUCTS_BY_CUSTOMER WITH SCHEMABINDING AS

      SELECT

            customers.companyname,

            products.productname,

            sum(odetail.unitprice*odetail.quantity) as TotalPurchase,

            count_big(*) as cnt

      FROM

            dbo.[order details] as odetail

            INNER JOIN dbo.orders as omain      on omain.orderid = odetail.orderid

            INNER JOIN dbo.customers as customers on customers.customerid = omain.customerid

            INNER JOIN dbo.products as products on products.productid = odetail.productid

      group by

            customers.companyname,

            products.productname

Go

First we will drop index if any of same name on this view as follows

DROP INDEX PRODUCTS_BY_CUSTOMER.PRODUCTS_BY_CUSTOMER_UNIQUE

DROP INDEX PRODUCTS_BY_CUSTOMER.PRODUCTS_BY_CUSTOMER_1

DROP INDEX PRODUCTS_BY_CUSTOMER.PRODUCTS_BY_CUSTOMER_2

   Now We will first chheck if our view is indexable or not then we add a clustered index on COMPANYNAME, PRODUCTNAME columns in our view further we will add a non-clustered index on ‘COMPANYNAME’ & ‘PRODUCTNAME’

IF ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1

BEGIN

      -- Create a clustered index, it MUST be unique

      CREATE UNIQUE CLUSTERED INDEX PRODUCTS_BY_CUSTOMER_UNIQUE ON PRODUCTS_BY_CUSTOMER(COMPANYNAME, PRODUCTNAME)

      EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

      -- Create NonClustered Indexes

      CREATE INDEX PRODUCTS_BY_CUSTOMER_1 ON PRODUCTS_BY_CUSTOMER(COMPANYNAME)

      EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

      -- Create NonClustered Indexes

      CREATE INDEX PRODUCTS_BY_CUSTOMER_2 ON PRODUCTS_BY_CUSTOMER(PRODUCTNAME)

      EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

END

Output in Query Analyzer:

The Message Windows Output:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Order Details'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Orders'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customers'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Products'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysallocunits'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysrowsets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PRODUCTS_BY_CUSTOMER'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysallocunits'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysrowsets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PRODUCTS_BY_CUSTOMER'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysallocunits'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysrowsets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     The ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1 in the above code listing. This command will tell if all of the requirements for indexing a view have been met so that we can programmatically determine if a view can be indexed or not.

    No space is allocated in the database for this view until the clustered index is created.

If we try to use the SP_SPACEUSED stored procedure on a view that is not indexed, we will get an error.

     After creating an index view, when we call select on this view it may use base table for constructing result set or may use stored data in indexed view this is determined by sqlserver at runtime. When number of record in indexed view are less usually they are directly fetch from base table using query we used to define the view but if records grow larger it will use stored records from indexed view. We can force SQL Server to use data stored in indexed view rather than constructing result set from base table & its query by using noexpand hint in our query on indexed view.

select * from products_by_customer with (noexpand)

  This forces the query optimizer to consider only the view and its indexes in the execution plan.

On the contrary

select * from products_by_customer option (Expand Views)

  This query forces query optimizer not to consider any View Indexes in the execution plan but use underlying tables & query which defined this view

When to Use indexed view?

If our application has

• Joins and aggregations of big tables
• Repeated patterns of queries
• Repeated aggregations on the same or overlapping sets of columns
• Repeated joins of the same tables on the same keys

then creating indexed view will be performance gain. Further if we create a de-normalized table by combining normalized tables in this case we can create views on this de-normalized table which resemble the old participating normalized tables then add appropriate indexes on these views.

When not to use indexed view?

   Storage is a major consideration as the data in the indexed view is physically and permanently stored not only in its base table, but in the clustered index of the View Index.

   Databases with frequent inserts, updates, deletes suffer as these commands must not only process the table, but any associated Indexed Views that the base table is participating in

     Top, Min, Max, Count, using another view, union, subqueries and outer joins are not allowed in indexed view that is restriction which disqualify many proposed index views.

Closing Point:

The Base tables of the view must be in the same database as the view. Two-part name used for all base tables while creating index view query.