Understanding SQL Server Indexed Views
#DecodeITeS
Index views are also called Materialized views.
Regular \ Standard views save the query to hide query complexity and business logic however does not give any query performance whereas indexed views save query along with data like a table.
Indexed views give performance benefit on index execution on the cost of extra overhead on underlying tables. Each data change that happens on the table will automatically be changed in an indexed view. On each DML operation, DB engines need to update the corresponding indexed view index that causes extra overhead in writing or deleting data from base tables.
While creating indexed views, you need to mention all objects referenced in 2 parts (SchemaName.ObjectName) names.
You cannot reference other views or tables from other databases while creating indexed views.
Use of EXISTS, NOT EXISTS, COUNT(*), OUTER JOIN, UNION, MIN, MAX, Subqueries, Table Hints & TOP is not possible with an indexed view.
Data Types text, ntext, image, and XML cannot be used in indexed views
You should remember 2 powerful hints EXPAND and NOEXPAND while working on performance issues of table hint. EXPAND hint force DB engine not to use the index of indexed view and expand the search to tables for netter indexes. In opposite to it, NOEXPAND restricts the DB engine to use indexed view index only.
Prerequisites to Create Indexed View
There are 2 Prerequisites to Create Indexed View:-
Schemabinding View – View should be created with schema binding option. Schemabinding views bind the table structure and you cannot name any modification. This is to ensure that your indexed view will not be impacted due to structural changes in the table. In case of any change needed, You need to drop view.
Unique Clustered Index – Once your Schemabinding view is created, You need to create a Unique Clustered Index on view. This index will maintain the data for your view.