SQL Server indexed view limitations

A SQL Server View is logical database object made up of a SQL Statement. It can also be called a virtual table made up of a SQL statement which may have data from one or more physical table. A view is generally created for

–          Security

Restrict users to particular row or column from certain tables and not all data.

–          Ease of Use

Create a view for a complex/tedious query joining multiple tables. Sometimes, it’s easy to use a single select against the view instead of a complex select statement.

–          Aggregation

Sometimes it’s easy to aggregate data (sum/max/min/computed columns) joining multiple table into a single view.

An index view can be created by creating a unique clustered index on a view column. An index view may improve query performance it is stored in database same as a table with clustered index. However, it comes with certain limitations

–          The underlying tables and columns can’t be modified as the view is created with SCHEMABINDING option.

–          An indexed view can’t have outer joins, TOP, COUNT  and other keywords (http://msdn.microsoft.com/en-us/library/ms191432.aspx)

–          The indexes on view require maintenance same as index on tables.

–          It can’t reference views or tables from different database

–          It might not be suitable for an OLTP system with frequent insert, updates and deletes.


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Leave a Reply

Your email address will not be published. Required fields are marked *