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
