Indexed views in SQL Server

In this SQL Server tutorial, we will discuss the indexed views in SQL Server. I will explain what are indexed views, how to create them, and when to use them.

• What is an indexed view in SQL Server?
• SQL Server indexed view performance
• When to use indexed views?
• How to create index on view in SQL Server?
• SQL Server create indexed view permission
• Syntax to create indexed view in SQL Server
• Create indexed view in SQL Server example
• SQL Server indexed view refresh
• Difference between view and indexed view in SQL Server
• SQL Server materialized view vs indexed view
• Advantages of indexed views in SQL Server
• Indexed view SQL Server limitations

What is an indexed view in SQL Server?

An indexed view in SQL Server is a view on which a unique clustered index is created. We define a clustered index on a view so that it can be stored in the database like any other table in the database.

You might think that a view is a logical structure that is not stored in the database. But there are some scenarios when you need to store this logical structure in the database. We will discuss the scenarios in the upcoming sections.

But, first, let us discuss how the indexed views help to increase the performance of the database.

Also, read: View in SQL Server

SQL Server indexed view performance

Suppose you have defined a view on multiple tables, having large data, a large number of joins, and aggregate functions. In that case, you should use the indexed views.

This is because when you create an indexed view, the results are stored physically in the database and you do not need to query the underlying tables every time you need to access the data.

As a result, the performance is increased because you are querying only one database object i.e. the indexed view.

When to use indexed views?

The indexed views increase the performance of the database queries if used in appropriate scenarios.

You should use the indexed views in a scenario where you need to join or aggregate data from a large number of tables.

However, you should not use the indexed views in a situation where you need to change the data in the base tables very frequently. Because in this situation, when the data will be updated frequently in the base tables, the indexed view will also be updated.

This will decrease the performance of the database.

Read: Arithmetic operators in SQL Server

How to create index on view in SQL Server?

In this section, I will mention some points that you should consider before creating an indexed view in SQL Server. When you create an indexed view, you have to verify a few things. These are:

• Firstly, you have to verify the SET options for the tables are correctly set.
• Secondly, the SET options for the current session or transaction also needs to specified correctly.
• While creating the view, you have to use the WITH SCHEMABINDING option.
• The owner of the view and the owner of the underlying tables must be the same.
• A unique clustered index must be created. Once a unique clustered index is created, you can create more nonclustered indexes.

Read: Full-text search in SQL Server

SQL Server create indexed view permission

• You need to have the CREATE VIEW permission in the database to creatre a view.
• Secondly, the ALTER permissions on the underlying schema are required.
• Also, if the underlying table belongs to another schema the REFERENCES permissions on the table are also required.

Syntax to create indexed view in SQL Server

The following is the syntax to create an indexed view in SQL Server:

CREATE VIEW <view name>   
WITH SCHEMABINDING   
AS  
SELECT Column1, Column2, Column3   
FROM <table name>
GO  
CREATE UNIQUE CLUSTERED INDEX <index name> ON <view name>(Column1)  
GO

• In the above syntax, firstly we are creating a view where <view name> is the name of the indexed view.
• The WITH SCHEMABINDING option is required while creating an indexed view.
• After the AS keyword, you have to specify the base table(s).
• Once the view is created, you hace to create a unique clustered index using the CREATE UNIQUE CLUSTERED INDEX command. In this command you have to specify a name for the clustered index and the column name(s) on which you are creating the index.

Read: How to check if SQL Server is running

Create indexed view in SQL Server example

Now let us see an example. I will create an indexed view in my database. Look at the below T-SQL code:

USE BackupDatabase
GO
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
   DROP VIEW Sales.vOrders ;
GO
CREATE VIEW SalesLT.vOrders
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Saleslt.vOrders (OrderDate, ProductID);
GO

The above T-SQL code will create a view and then a clustered index will be created on it.

To verify that the view is created, you can retrieve data from it using a SELECT query like below:

USE BackupDatabase
GO
SELECT * FROM SalesLT.vOrders

To verify if the view is indexed, you can execute a query like the below:

USE BackupDatabase
GO
SELECT * FROM 
sys.views v
JOIN
sys.indexes i On v.object_id = i.object_id

You can see that the view that we created above is indexed in the database.

Thus, you might have learned how we can create an indexed view in a SQL Server database.

Read: How to delete a view in SQL Server

SQL Server indexed view refresh

In SQL Server, you do not need to refresh the indexed view manually as you can do in the other databases.

In other databases, you can specify the time interval after which the materialized view can be updated. But, in SQL Server the indexed views are refreshed automatically when the underlying tables are updated.

Difference between view and indexed view in SQL Server

Although we have discussed some points about the views and the indexed views, if you want a clear difference between a standard view and an indexed view, you can read below the points of differences between a standard view and an indexed view:

View	Indexed View
The data returned by a standard view is not stored physically in the database	The data returned by an indexed view is stored physically in the database.
It is like storing a query and running it later.	It is stored as a structure like a table.
It requires more computing resources while running.	It requires more storage and less computing resources if the data is only retrieved.
Easy to maintain	Requires some maintenance as it is a physical object in the database.

Thus, you might have got the difference between a standard view and an indexed view.

Read: How to see view definition in SQL Server

SQL Server materialized view vs indexed view

The concept of the materialized view and the indexed view is the same. i.e. both are stored physically in the database. When a view is materialized i.e. stored physically in the database, it is called an indexed view in SQL Server.

However, the name materialized view is used in Azure Synapse Analytics. When you store a view physically in the database, it is called a materialized view.

Read: How to insert a row in a view in SQL Server

Advantages of indexed views in SQL Server

Till now we have discussed how the indexed views work in SQL Server. Now let us see how beneficial are these if used in the desired condition:

• The indexed views increase the query performance i.e. retrieveing data becomes fast if the indexed view is created by creating joins over multiple tables. This is because, while retrieving the data, only the indexed view is queried and not the underlying tables.
• If your indexed view has used some aggregate functions, the operations are perfromed only once when the data is updated. As a result, when the next time data is retrieved, the stored result of the operations or calculations is retrieved instead of calculating it again.
• A layer of abstraction is achieved. This means the data can be retrieved from the indexed view without knowing the underlying database structure i.e. joins on multiple tables or use of aggregate functions.

Read: Comparison Operators in SQL Server

Indexed view SQL Server limitations

Now let us highlight the drawbacks of indexed views. The following are some challenges that we face with the indexed views:

1. You should not use the indexed views if the data in the underlying tables is changed very frequently. If there are frequent write operations on the underlying tables, the indexed views are updated everytime. This will decrease the performance instead of increasing.
2. There are a lot of restrictions while creating the indexed views. You have to follow all the restructions in order to create an indexed view.
3. Creating indexed views require extra maintenance as these are stored physically in the database.
4. In any non-enterprise SQL Server version, you have to use the option WITH(NOEXPAND) otherwise SQL Server will expand the view and ignore the index i.e. it will behave as a standard view.
5. The storage requirements are increased as the index views are stored physcially.

You may also like to read the following tutorials on SQL Server.

• How to view table in SQL Server
• Alter view in SQL Server
• SQL Server logical operators
• View SQL Server Error Logs
• Create a table from view in SQL Server

Thus, you might have learned what are indexed views in SQL Server, how to create them, and various features of an indexed view.

• What is an indexed view in SQL Server?
• SQL Server indexed view performance
• When to use indexed views?
• How to create index on view in SQL Server?
• SQL Server create indexed view permission
• Syntax to create indexed view in SQL Server
• Create indexed view in SQL Server example
• SQL Server indexed view refresh
• Difference between view and indexed view in SQL Server
• SQL Server materialized view vs indexed view
• Advantages of indexed views in SQL Server
• Indexed view SQL Server limitations