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

Leave a Reply

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