Indexed View in SQL Server

Indexed View in SQL Server with Examples

In this article, I am going to discuss the Indexed View in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed how to use the Check Encryption and Schema Binding Options in SQL Server Views with examples. As part of this article, we are going to discuss the following pointers.

  1. What is an Indexed View in SQL Server?
  2. Example to understand SQL Server Index View.
  3. Rules for creating an Index view in SQL Server
  4. How to Create an index on a view
  5. Advantages and Disadvantages of Indexed View.
  6. Comparing the performance using both index and without indexed views.
  7. When to use Indexed View in real-time applications?
What is an Indexed View in SQL Server?

Indexed View is a virtual table whose result set (output of a select statement) is persistent on the hard disk. In general, when we create a view, the view does not store any data by default. So, when we query a view, it actually queries the underlying base table and gets the data. But we can change this default behavior in SQL Server i.e. the SQL Server Views can store the data physically. In order to do this, first, you need to create an index on the view.

When we create an index on a view, then the view is called Index View. In the case of Indexed views, the result set (output of select statement which includes pre-calculated data like aggregation, summation, count, etc.) is persisted on the hard disk. As the data is persisted on the hard disk, now the SQL Server engine does not have to go again and again and get the data from the underlying base table. So what it does is it gets the data that is already persisted in the index view virtual table. And because of this, it saves lots of overheads.

Indexed View in SQL Server

Note: Indexed View is not always suggested for all situations. For example, if your table is highly transactional (i.e. the base table having lots of Insert, Update and Delete operations), then the indexed view is not suggested. This is because the synchronization between the base table and the indexed view is a time-consuming task.

Example to Understand the Index View in SQL Server.

Let us understand the Indexed View in SQL Server with an example. We are going to use the following Product and ProductSales table to understand the Indexed Views in SQL Server.

Index View in SQL Server

Please use the below SQL Script to create and populate the Product and ProductSales table with some test data.

-- Create table Product
CREATE TABLE Product
(
  ProductId INT PRIMARY KEY,
  Name VARCHAR(20),
  UnitPrice INT
)
GO

-- Populate Product table with some test data
INSERT INTO Product VALUES(1, 'Books', 40)
INSERT INTO Product VALUES(2, 'Pens', 30)
INSERT INTO Product VALUES(3, 'Pencils', 10)
GO

-- Create table ProductSales
CREATE TABLE ProductSales
(
  ProductId INT,
  QuantitySold INT
)
GO

-- Populate ProductSales table with some test data
INSERT INTO ProductSales VALUES(1, 10)
INSERT INTO ProductSales VALUES(3, 23)
INSERT INTO ProductSales VALUES(3, 21)
INSERT INTO ProductSales VALUES(2, 12)
INSERT INTO ProductSales VALUES(1, 13)
INSERT INTO ProductSales VALUES(3, 12)
INSERT INTO ProductSales VALUES(2, 13)
INSERT INTO ProductSales VALUES(1, 11)
INSERT INTO ProductSales VALUES(2, 12)
INSERT INTO ProductSales VALUES(1, 14) 
GO
Example:

Create a view that will return the Total Sales and Total Transactions by Product. The output should be as shown below. 

SQL Server Indexed View

Please use the below SQL Script to Create the view:

CREATE VIEW vwTotalSalesPriceByProduct
WITH SCHEMABINDING
AS
SELECT	Name, 
  COUNT_BIG(*) AS TotalTransactions,
  SUM(ISNULL((QuantitySold * UnitPrice), 0)) AS TotalSalesPrice		
FROM dbo.ProductSales prdSales
INNER JOIN dbo.Product prd
ON prd.ProductId = prdSales.ProductId
GROUP BY Name
Rules for creating an Index view in SQL Server:
  1. The view should be created with the SCHEMABINDING option
  2. If an Aggregate function is used in the SELECT LIST which references an expression, and if there is a possibility for that expression to become NULL, then, a replacement value should be specified. In this example, we are using ISNULL() function, to replace NULL values with ZERO with the expression SUM(ISNULL((QuantitySold * UnitPrice), 0)) AS TotalSalesPrice
  3. If the GROUP BY clause is specified, then the view select list must contain a COUNT_BIG(*) expression
  4. The base tables in the view should be referenced with 2 part name. In this example, Product and ProductSales are referenced using dbo.Product and dbo.ProductSales respectively.

The view which we created satisfied all the above rules. So we can create an index on the above view.

Creating an index on a view:

The first index that we create on a view must be a unique clustered index. After the unique clustered index has been created, then we can create additional non-clustered indexes.

CREATE UNIQUE CLUSTERED 
INDEX UIX_vwTotalSalesPriceByProduct_Name
ON vwTotalSalesPriceByProduct(Name) 

Now we have an index on the vwTotalSalesPriceByProduct view, so the view gets materialized. The data is stored in the view. So when we execute

Select * from vwTotalSalesPriceByProduct

The data is returned from the view itself, rather than retrieving data from the underlying base tables. The Indexed view in SQL Server can significantly improve the performance of the queries that involve Joins and Aggregations. The cost of maintaining an indexed view is much higher than the cost of maintaining a table index. 

Advantages and Disadvantages of Indexed View:

First, we will create one table and then perform the Insert and Select operations without indexes and needs to log the logical reads. Then we will do the same operations on the same table but with indexes and also needs to log the logical reads. Finally, we will create one indexed view and will perform the same Insert and Select operations on the view and will also log the logical reads. Then we will compare the logical reads of the above three scenarios and will come to the conclusion that in which scenarios indexed views are good and in which scenarios indexed views are bad.

When to use Indexed View in real-time applications?

Step1: Without Indexes

So, create the following employee table without any kind of keys or indexes.

CREATE TABLE Employee(
 [EmployeeID] [int] NOT NULL,
 [EmployeeName] [varchar](50) NULL,
 [EmployeeCode] NUMERIC(18, 0) NOT NULL,
 [JoiningDate] DateTime NOT NULL
);

Once you created the above Employee table, let us insert some data into the table. We are going to perform some kind of grouping operations on the Joining Date column. So, let’s insert some data accordingly. So, please execute the following SQL Script.

DECLARE @CODE NUMERIC(18, 0);
DECLARE @Name VARCHAR(50);
DECLARE @ID INT;
DECLARE @JoiningDate DATETIME;
SET @CODE = 1000;
SET @ID = 1;
WHILE @CODE < 40000
BEGIN
   SET @Name = 'Name - ' + CAST(@ID AS VARCHAR(10))

   IF(@CODE < 5000)
   BEGIN
        SET @JoiningDate = '2012-01-18';
        INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END
   ELSE IF(@CODE < 15000)
   BEGIN
        SET @JoiningDate = '2014-11-05';
        INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END
   ELSE IF(@CODE < 25000)
   BEGIN
        SET @JoiningDate = '2018-05-07';
        INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END
   ELSE IF(@CODE < 30000)
   BEGIN
        SET @JoiningDate = '2016-10-22';
        INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END
   ELSE IF(@CODE < 35000)
   BEGIN
        SET @JoiningDate = '2015-03-18';
        INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END
   ELSE
   BEGIN
       SET @JoiningDate = '2010-09-09';
       INSERT INTO Employee VALUES(@ID, @Name, @CODE, @JoiningDate);
   END

   SET @ID = @ID + 1
   SET @CODE = @CODE + 1
END;
Example: Select Operation without indexes

Please execute the below SQL Script, which will group the employee count by Joining Date. Here, we are executing with SET STATISTICS IO ON to see the logical reads.

SET STATISTICS IO ON;
SELECT JoiningDate, COUNT(*) NoOfJoining FROM Employee GROUP BY JoiningDate;

Once you execute the above query, open the Message window which appears next to the Results window and see the logical reads count as shown below.

Select Operation without indexes

As you can see without any indexes the logical reads are 231 in the case of select operations.

Example: Insert Operation without indexes

Now let execute the following Insert statement and see the logical reads.

SET STATISTICS IO ON;
INSERT INTO Employee VALUES(39001, ‘Name – 39001’, ‘40000’, ‘2010-09-09’);

Once you execute the above query, see the message window which should display the logical reads as shown below.

Insert Operation without indexes

So, without any indexes, the logical reads are 1 in the case of Insert operations.

Step2: With Indexes

So, let us first create a clustered index on the JoiningDate column of the Employee table as we are grouping the data based on this column. Please execute the below SQL Script which will create one clustered index on the Employee table.

CREATE CLUSTERED INDEX IX_Employee_JoiningDate ON Employee (JoiningDate);

Once you created the clustered index, let us perform the same operations as we performed without indexes.

Example: Select Operation with Indexes

SET STATISTICS IO ON;
SELECT JoiningDate, COUNT(*) NoOfJoining FROM Employee GROUP BY JoiningDate;

Please have a look at the message window.

Select Operation with Indexes

So, with the clustered index, in the case of select operations, the logical reads are 253.

Example: Insert Operation with Indexes

SET STATISTICS IO ON;
INSERT INTO Employee VALUES (39002, ‘Name – 39002’, ‘40001’, ‘2010-09-09’);

Please have a look at the message window as shown below once you execute the above query.

Insert Operation with Indexes

As you can see in the above image, with indexes, the logical reads are increased to 8 in the case of Insert operations.

Step3: With Indexed View

So, first, we need to create an indexed view and then we will perform the same operation on the indexed view.

Creating Indexed View:

CREATE VIEW IndexedView
WITH SCHEMABINDING
AS
SELECT  dbo.Employee.JoiningDate, 
        COUNT_BIG(*) AS NoOfJoining 
 FROM dbo.Employee 
 GROUP BY dbo.Employee.JoiningDate

Once you create the above, in order to make the above view as an Indexed view, we need to create a unique clustered index on this view. So, please create a unique clustered index by executing the below SQL Script.

CREATE UNIQUE CLUSTERED INDEX IX_IndexedView_JoiningDate ON IndexedView (JoiningDate);

Once you created the above unique clustered index, now your view becomes indexed view and now it stores the data physically. So, let us perform the same Insert and select operations on the above Indexed View.

Example: Select Operation on Indexed View

Please execute the following SQL query using the SET STATISTICS IO ON to see the logical reads. Here, we are using the indexed view instead of the Employee table.

SET STATISTICS IO ON;
SELECT JoiningDate, NoOfJoining FROM IndexedView;

Once you execute the above select statement, open the Message window and see the logical reads count as shown below.

Select Operation on Indexed View

What happened? With Indexed view, it is showing the logical reads 231. This is because; this feature is only available on SQL Server Enterprise Edition and here, I am using SQL Server Standard Edition. So, if you are using the SQL Server Developer or Standard Edition, you must use then you need to use the WITH (NOEXPAND) table hint directly in the FROM clause of the query as shown below:

SET STATISTICS IO ON;
SELECT JoiningDate, NoOfJoining FROM IndexedView WITH (NOEXPAND);

Now check the logical reads in the message window as shown below.

Indexed View in SQL Server

As you can see, it drastically reduces the number of logical reads to 2. This is because now it gets the data directly from the indexed view.

Example: Insert Operation with Indexed view

Now let us execute the following Insert statement and see the logical reads.

SET STATISTICS IO ON;
INSERT INTO Employee VALUES(39003, ‘Name – 39003’, ‘40002’, ‘2010-09-09’);

Once you execute the above query, see the message window which should display the logical reads as shown below.

Insert Operation with Indexed view

So, with indexed views in place, when we insert a record into the Employee table, the logical reads are drastically increased to 20 which are shown in the above image.

So, when we create an indexed view, the select query performance increases drastically. On the other hand, the DML operations (Insert, Update, and Delete) performance decrease drastically.

When to use Indexed View in SQL Server?

Indexed views are ideal for scenarios, where the underlying data is not frequently changed. Indexed views are more often used in OLAP systems because the data is mainly used for reporting and analysis purposes. The Indexed views may not be suitable for OLTP systems, as the data is frequently added and changed.

In the next article, I am going to discuss the Triggers in SQL Server with examples. Here, in this article, I try to explain the Indexed View in SQL Server step by step with examples. I hope you enjoy this Indexed View in SQL Server with Examples article. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *