Index on Views

A normal view does not have any physical representation of its data in SQL Server, It stores as a just metadata information pointing to the underlying tables. But if you created View with unique clustered index then SQL Server will physically materialize the view’s data.

Advantage of Indexed View

• Indexed views gives great performance benefits for queries to retrieve data.
• Indexed views can substantially reduce the amount of I/O required to return data
• SQL Server keeps the indexed view in sync with modifications against the underlying tables. You cannot request to synchronize the view’s contents on demand or on scheduled basis. An indexed view is just like a table index.

Rules for Creating Indexed View

• The Indexed View must have one unique and clustered.After creating a clustered index on a view, you can create additional non-clustered indexes like table.
• The view must be created with the SCHEMABINDING option.

If you want to optimize queries that select data from the employee and department for employees.you can go for Indexed View following code creates the employee indexed view based on a query that joins tbl_emp and tbl_dept.

CREATE VIEW dbo.employee SCHEMABINDING
AS
SELECT emp.emp_id, emp.emp_name ,dept.name
FROM tbl_emp emp
JOIN tbl_dept dept ON emp.dept_id = dept.dept_id

GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_empid ON dbo.employee(emp_id)

SQL Server doesn’t regenerate the whole index whenever the underlying tables are modified. It maintains the index in a smarter manner. When you insert data, SQL Server identifies the affected row of the view and update in the Index.