This blog talks about Indexed Views with their usage, benefits and constraints in SQL Server.
What is Indexed View?
We all know about views in Sql Server. What does Indexed view mean? This is also a simple view which has a unique clustered index defined on it.
When a clustered index is created on a view, the result set is stored in the database just like a table with a clustered index.
Why do we need Indexed View?
The main use of creating a unique clustered index on a view is to improve query performance because the view is stored in the database in the same way a table with a clustered index is stored and also the sql query optimizer automatically decides when an indexed view can be used for a query execution.
It will improve the performance of queries that have joined and aggregation operations which are frequently performed by many queries.
Pre-requisites
There are some pre-requisites to be followed before using this type of views in our applications. First, we need to create a unique clustered index for the view. Second, the view must reference only base tables that are in the same database as the view. Third, the view must be created using the WITH SCHEMABINDING option.
Having the clustered index of the view be unique improves the efficiency by finding the rows in the index that are affected by any data modification.
When to use Indexed View?
The best scenario for using Indexed views is when the underlying data is not frequently updated. In general, maintaining an indexed view can be greater than the cost of maintaining a table index. If the data is more frequently being updated, then it doesn't add up to any advantage of using this view since there is a huge cost associated in maintaining the indexed view data associated.
How to create Indexed View?
An Indexed view can be created in the same way how we generally create the regular views.
Syntax
- CREATE VIEW ViewName
- WITH SCHEMABINDING
- AS
- SELECT ColumnA, ColumnB, ColumnC
- FROM dbo.MyTable
- GO
- CREATE UNIQUE CLUSTERED INDEX idx_ViewName ON ViewName(ColumnA)
- GO
Example
- CREATE VIEW vCustomerOrders
- WITH SCHEMABINDING
- AS
- SELECT C.CustomerNo,
- C.FirstName,
- C.LastName,
- OH.OrderNo,
- OH.OrderDate,
- OD.ProductId,
- P.ProductName,
- OD.QrderQty
- FROM PurchaseDB.OrderHeader OH
- INNER JOIN PurchaseDB.OrderDetails OD ON OH.OrderNo = OD.OrderNo
- INNER JOIN PurchaseDB.Product P ON P.ProductId = OD.ProductId
- INNER JOIN PurchaseDB.Customer C ON OH.CustomerNo = C.CustomerNo
- GO
- CREATE UNIQUE CLUSTERED IX_vCustomerOrders
- ON PurchaseDB.vCustomerOrders(CustomerNo, OrderNo, ProductId);
- GO
In this example, a unique clustered index is created using the columns CustomerNo, OrderNo and ProductId and all of these tables exists in the same PurchaseDB custom database.
What are the constraints of Indexed View?
Indexed views do come with certain constraints which we need to carefully review before deciding to use them.
- User who executes CREATE INDEX statement must be the owner of the view.
- All tables must be referenced by two-part names, schema.tablename in the view definition.
- Cannot reference other views.
- If GROUP BY clause exists, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.
Summary
Indexed views would be useful for improving the performance of our queries when used appropriately. I hope this blog helps you to understand and use Indexed views.