Tag Archives: like materlized view in SQL Server

Indexed View in Sql Server

Indexed View

A view with a unique clustered index is an indexed view. It will save data for better performance and utilized the index on the query. It help SQL Server to increase performance. It act like materialized view in oracle.

As base table is updated the indexed view is also updated.

View: A view is the normal representation of SQL queries. It is saved in data dictionary of the database. It is used to keep the complex query for reuse purpose, security purpose( if not want to represent the exact structure of the database to real world).

View does not have its own data it will fetch data from its base tables.

Example for creating the Index view:

1. Create view on the SQL Query:

CREATE VIEW index_view_name AS
SELECT columnA, columnB FROM dbo.table_name

2. Create unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_index_view_name ON index_view_name(columnA)