Indexed View in SQL Server

We have already understood about the standard Views and types of Standard View in my previous post. If you want to know about Standard Views please Click here to know about Standard Views

In this article I will talk about Indexed Views. What is Indexed View?

When we create Index on Simple or Complex view, we call that view as Indexed View. If we create Unique Clustered index on any view that View will be called as Index View. Indexed Views are also known as Materialized view, this means result set of indexed view is computed and stored in the database just like a table with a clustered Index is stored.

 If our View is created from multiple table joins and has aggregation that process many records in that case Query performance will be slow. Indexed views can improve query performance drastically.

Indexed view can’t improve query performance for the those queries which are running against a database which has multiple Writes and Updates ,Queries which don’t has join and aggregations.

If you want to create a Clustered Index on your view then you have to make sure that your view was created with Schemabinding option. I have already discussed about the Schemabinding in my previous post Click here to know about Schemabinding option on Views. If your view was not created with Schemabinding option then you can’t create Index on non Schemabinding views.

Let’s take one example to explain the concept.

/*CREATING A NEW DATABASE*/

USE MASTER

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER

/*CREATING TWO NEW TABLES AND POPLUTAING DATA*/

GO

USE TEACHMESQLSERVER

GO

CREATE TABLE DEP (DEPID INT PRIMARY KEY,DEPNAME VARCHAR(MAX))

GO

CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT FOREIGN KEY REFERENCES DEP(DEPID))

GO

INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)

/*CREATING A NEW COMLEX VIEW*/

GO

IF EXISTS(SELECT NAME FROM SYS.VIEWS WHERE NAME='COMPLEXVIEW')

DROP VIEW COMPLEXVIEW

GO

CREATE VIEW COMPLEXVIEW

WITH SCHEMABINDING

AS

SELECT DBO.EMP.EMPID,EMPNAME,DEPNAME FROM DBO.DEP INNER JOIN DBO.EMP ON DBO.DEP.DEPID= DBO.EMP.DEPID

/*REFERENCEING COMPLEX VIEW*/

GO

SELECT * FROM COMPLEXVIEW

/*CREATING UNIQUE CLUSTERED INDEX*/

GO

CREATE UNIQUE CLUSTERED INDEX INDEXEDCOMPLEXVIEWIN ON COMPLEXVIEW  (EMPID ASC)

Congratulations you have successfully created a Indexed view. If you want to check this you can check from below queries

GO

SELECT * FROM SYS.all_views WHERE NAME='COMPLEXVIEW'

/*COPY OBJECT_ID FROM PREVIOUS QUERY AND PASS IT INTO BELOW QUERY*/

GO

SELECT * FROM SYS.indexes WHERE OBJECT_ID=101575400

You can find it using below query also

GO

SELECT * FROM SYS.indexes WHERE name='INDEXEDCOMPLEXVIEWIN'

I hope you enjoyed the tutorial; 

There are couple of topics which are related to Views don't forget to visit them.

Types of Views in SQL Server, How to Perform DML Operations on Views?

Indexed Views in SQL Server

Partitioned View in SQL Server