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.
What is an Indexed View in SQL Server?
A view in SQL Server is a virtual table. That means it does not store any data physically. So in other words, we can say that a view in SQL Server is nothing more than compiled SQL query. Every time, we issue a select query against a view, the view actually gets the data from the underlying base tables and not from the view. This is because the views in SQL Server itself does not contain any data by default. But we can change this default behavior. Means the SQL Server Views can also store the data physically. In order to do this, we need to create an index on the views.
When we create an index on a view, then the view gets materialized. It means, now the view store the data physically. We call this as Indexed View in SQL Server. Now, if we issue a select query against an indexed view in SQL Server, then the data is going to be retrieved from the view without having to go to the underlying table. This will make the select statement to work slightly faster. However, the disadvantage is INSERT, UPDATE and DELETE operations will become a little slow, because every time we insert or delete a row from the underlying table, the view index needs to be updated. In short, DML operations will have a negative impact on performance.
Let’s see an example for better understanding the Index View in SQL Server.
We are going to use the following Product and ProductSales table to understand the Indexed Views 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
Create a view which will return the Total Sales and Total Transactions by Product. The output should be as shown below.
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:
- The view should be created with the SCHEMABINDING option
- 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
- If the GROUP BY clause is specified, then the view select list must contain a COUNT_BIG(*) expression
- 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.
Let’s create 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.
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 limitations of Views in SQL Server with examples.
In this article, I try to explain the Indexed View in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.