Indexed View in SQL Server

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.

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.

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.

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.

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.

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.

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.

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.

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.