Normally a view is just a SQL query which has only its definition but no data on its own until the view is executed. However, SQL Server allows us to create indexes on a view to significantly improve the performance of data retrieval operations.
Let’s say you have a view which returns a comparatively larger result set. Using it in the joins of a SQL command may slowdown the performance, since each time the query executes the entire result set of the view needs to be processed in order to enforce the joins. Under these circumstances, creating indexes on the view can help you to improve the performance.
A view created with a unique clustered index is known as an “Indexed View” or “Materialized View”. Unlike views, an Indexed View exists on the disk like a table in which the clustered index is created. Once a clustered index is created you may create non-clustered indexes on the view.
As said earlier, Indexed Views exist on the disk like a table. So each time the source table is updated, then the view gets updated, and the indexes are recompiled. It becomes an overhead if the source table is highly transactional, so you must justify yourself using indexed views when you need them.
SQL Server needs certain options be set appropriately while an indexed view is created, when the base table of the view is modified, or during optimization of the indexed view. Below is the list of options and their required value.
| SET Options |
Required Value |
| ANSI_NULLS |
ON |
| ANSI_PADDING |
ON |
| ANSI_WARNINGS |
ON |
| CONCAT_NULL_YIELDS_NULL |
ON |
| QUOTED_IDENTIFIER |
ON |
| NUMERIC_ROUNDABORT |
OFF |
A view can be indexed only when it is deterministic so that it returns the same results from the same underlying data. A view is deterministic if its definition does not contain any non-deterministic functions or expressions in the select list as well as WHERE and GROUP BY clauses. You must ensure the following requirements while defining the view.
- The WITH SCHEMABINDING option must be used
- The base tables and user defined functions are mentioned with two-part names (schema_name.table_name / schema_name.function_name)
- SET options ANSI_NULLS and QUOTED_IDENTIFIER are set appropriately
Indexed View Example:
CREATE VIEW vwOrderDetails WITH SCHEMABINDING
AS
SELECT OD.OrderID, OD.ProductID, P.ProductName , OD.UnitPrice
, OD.Quantity, OD.Discount
FROM dbo.Products P
INNER JOIN dbo.[Order Details] OD
ON P.ProductID = OD.ProductID
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_vwOrderDetails]
ON vwOrderDetails (OrderID, ProductID)
GO
SQL Server does not allow you to use anything that would affect the deterministic property of the indexed view. So you must ensure NOT to use any of the following in your index view’s definition.
- Non-deterministic expressions or functions like GETDATE(), RAND()
- SUM on nullable expressions.
- Rowset functions.
- References to another view.
- Full-text Predicates like CONTAIN or FREETEXT
- Imprecise Constants like 8.65e4
- Sub queries, Self joins, Outer joins and inline or table valued functions
- Table hints like NOLOCK
- Non Unicode Collations
- ORDER BY clause
- GROUP BY ALL, if you use GROUP BY then make sure to include COUNT_BIG(*) in the select list
- CUBE, ROLLUP,GROUPING( ), HAVING
- UNION
- DISTINCT,TOP
- EXISTS, NOT EXISTS
- ANY, NOT ANY
- COMPUTE, COMPUTE BY