Microsoft Business Intelligence (Data Tools)|SQL

Indexed view is the most powerful feature of SQL which was introduced with SQL Server 2000. The main purpose of the indexed view is to run the queries faster and improve the performance of the application. Indexed view has a unique clustered index nature and let us pre-computes all sorts of joins, aggregations, and computations because we can’t use these conditions into every query.

Non-clustered indexes can also be created on the view. The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost.

Benefits of Views -

As we know about the views that they contain no saved data and they are only a saved query. Views have two main benefits such as -

1) They simplify query writing because they don't require you to directly access tables and because they often already include restrictions, computations, aggregations, and joins—saving you from retyping those conditions.

2) They provide a security mechanism, letting you give users access to an appropriate view of a table's data instead of access to the entire table.

A clustered index is the only type of SQL Server index that contains data; the clustered index on a view contains all the data that makes up the view definition. As soon as you create this clustered index, the view is materialized, meaning SQL Server allocates storage space for it.

Requirements for Indexed View

A view must meet the following requirements before you can create a clustered index on it:

The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic.

The view definition can't contain the following:

TOP
Text, ntext, or image columns
DISTINCT
MIN, MAX, COUNT(*), COUNT(<expression>), STDEV, VARIANCE, or AVG
SUM on a nullable expression
a derived table
the ROWSET function
another view (you can reference only base tables)
UNION
subqueries, OUTER joins, or self-joins
full-text predicates (CONTAIN or FREETEXT)
COMPUTE or COMPUTE BY

A view that is to be indexed has to be created with schema binding which indicates that once the indexed view is created on the underlying tables then these tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.

How to create Indexed View on the View

/*** Create table to verify the Indexed View ***/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[OrderMaster](

[Order No] int primary key,

[Order Date] [datetime] NULL,

[Year] [float] NULL,

[Item Name] [nvarchar](255) NULL,

[Sales Person] [nvarchar](30) NULL,

[Qty] [int] NULL,

[Unit Price] [decimal](16, 2) NULL,

[Tax Amt] [decimal](16, 2) NULL,

[Total Amt] [decimal](16, 2) NULL

) ON [PRIMARY]

/*** Create View with filter condition***/

Create VIEW [dbo].[ViewOrderMaster]

SELECT [Order No],

[Order Date], Year,

[Item Name], [Sales Person], Qty, [Unit Price],

[Tax Amt], [Total Amt]

FROM

dbo.OrderMaster

where

[Total Amt]<1500

/*** Create UNIQUE CLUSTERED INDEX ***/

CREATE UNIQUE CLUSTERED INDEX [Ind_CI_viewOrder] ON [dbo].[ViewOrderMaster]

(

[Total Amt] ASC

)

The result set of Indexed view is stored in the database just like any other clustered index after creating the unique clustered index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available.

After you create the unique clustered index, you can create multiple nonclustered indexes on the view.

Important facts–

Data that comprises the indexed view is persistent, with the indexed view storing the data in the clustered index's leaf level.
One of indexed views' most valuable benefits is that your queries don't have to directly reference a view to use the index on the view.
They analyze our workload so that we're familiar with the types of queries our users run.
They enable the proper SET options before creating any tables used in the view definition.
They use the OBJECTPROPERTY function's 'IsIndexable' option to make sure you can index the view.
They make sure the first index on the view is unique and clustered (other indexes don't have to be unique or clustered).

Difference between Filtered Index and Indexed View

Filtered indexes have the following advantages over indexed views-

Criteria	Filtered Index	Indexed Views
Maintenance Costs	Reduced index maintenance costs because the query processor uses fewer CPU resources to update a filtered index	The query processor uses more CPU resources to update a Indexed View.
Plan Quality	Improved plan quality because during query compilation, the query optimizer considers using a filtered index in more situations	They are not so benificial in the query optimizer as Filtered Index
Online index rebuilds	A Filtered indexes while they are available for queries	Online index rebuilds are not supported for indexed views
Non-unique indexes	Filtered indexes can be non-unique	Indexed views must be unique
Only One Table	A Filtered Index is created on column(s) of a particular table.	Index Views can be created on column(s) from multiple base tables.
Simple WHERE criteria	A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause, NOT IN, OR and dynamic / non-deterministic predicates like WHERE col >= DATEADD(DAY, -1, GETDATE()) are not allowed, only simple comparison operators are allowed.	This limitation does not apply to indexed views and you can design your criteria as complex as you want.