Views are used for several different reasons:
1. To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.
2. To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:
1. Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.
2. Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view.
Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.
Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.
Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.
Even though views can be a great tool for securing and customizing data, they can be slow. Indeed, they are not any faster than the query that defines them. With SQL Server 2000, indexed views (also referred to as "materialized" views) are supported to overcome this limitation.
Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views. All views should be created against base tables
From the database management system (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for executing the query. The query optimizer searches though a set of possible execution plans for a query, and it chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.
In the case of a nonindexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view [Note1] . After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.
Indexed views can increase query performance in the following ways:
- Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
- Tables can be prejoined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
Secondary, nonclustered indexes on views can provide additional query performance. Similar to nonclustered indexes on tables, nonclustered indexes on views may provide more options for the query optimizer to choose from during the compilation process.
Applications that benefit from the implementation of indexed views include:
- Decision support workloads.
- Data marts.
- Data warehouses.
- Online analytical processing (OLAP) stores and sources.
- Data mining workloads.
From the query type and pattern point of view, the benefiting applications can be characterized as those containing:
- Joins and aggregations of large tables.
- Repeated patterns of queries.
- Repeated aggregations on the same or overlapping sets of columns.
- Repeated joins of the same tables on the same keys.
- Combinations of the above.
Adding indexes to the schema increases the overhead on the database because the indexes will require ongoing maintenance.
Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. Online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.
Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While you tune the physical design of a database, tradeoffs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Database Tuning Advisor should be used wherever it is possible.
Query optimization cost can increase substantially if there are many indexed views that the query optimizer may consider for a particular query. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. Each view has to be investigated for the potential substitution before it is rejected. This may take some time, especially if there are hundreds of such views for a given query.
Indexed View Background:
Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced just like tables in SQL statements. The result set of a standard view is not stored permanently in the database. Every time a query references a standard view, SQL Server substitutes the definition of the view into the query internally until a modified query is formed that only references base tables
From DBMS perspective, a view is a description of the data, a form of metadata. It is possible to create a unique clustered index on a view, as well as non-clustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view. The main benefit of using index view is that the query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.
In the case of a non-indexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view. After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or the entire query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.
Advantages of Indexed View:
Indexed views improve the performance in the following ways:
- Joins and aggregations that process many rows.
- Join and aggregation operations that are frequently performed by many queries.
- Decision support workloads.
- The real benefits of Indexed Views are when we have aggregates that are too expensive to compute in real time.
- Aggregations can be pre-computed and stored in the index to minimize expensive computations during query execution.
- Tables can be pre-joined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
- Indexed views may be substituted by the query optimizer if it is determined that the cost of the query will be less than using the base table.
For a standard view, the overhead of dynamically building the result set for each query that references a view can be significant for views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, we can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.
Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.
As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification. The capability of the query optimizer to take advantage of indexed views when it processes queries has improved over earlier versions when both the query and the view definition contain the following matching elements:
Disadvantages of Indexed View:
To use an indexed view in all other editions, the NOEXPAND table hint must be used. Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.
Another cost of indexed views is that the data is actually stored. By applying the clustered index we are creating a copy of the data. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.
Following are the disadvantages using index views that do not improve the performance:
- OLTP systems that have many writes to the disk.
- Databases that have many updates.
- Queries that do not involve aggregations or joins.
- High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
- The overuse of indexed views can be like winding a watch with a wrench.
- Online index rebuilds are not supported for indexed views.
I agree with Lutz that Filtered index is better and top of his comments on filtered index versus indexed view; we can create our filtered index as a non-unique index and where as indexed views can only be created as a unique index. One point to keep in mind is we cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. With filtered index, online index rebuilds is possible. We can rebuild filtered indexes while they are available for queries. Online index rebuilds are not supported for indexed views.
When To Use A View
You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.
- To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
- To control access to rows and columns of data.
- To aggregate data for performance.
Let’s take a look at each of these scenarios.
Complexity and Customization
Taking care of complex joins and filtering rules inside of a view can benefit other users. As an example, consider the following view from the Northwind database.
CREATE VIEW "Order Details Extended" AS
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
"Order Details" ON
Products.ProductID = "Order Details".ProductID
A business user with an ad-hoc reporting tool can take advantage of the above view in building customized reports to support her goals. She can use the view to see all of the details about an order without finding the tables to join for product and order information, and without performing the calculation for the price discount. Not only does this make the database easier for the end user, but it also allows a DBA to make changes to the underlying tables without breaking end user reports.
View can also be useful to novice SQL developers on your team. Although stored procedures can remove complexity from a database only a view can be the target of an INSERT, UPATE, or DELETE statement thus functioning more like a real table. There are restrictions to modifying data through views. For more information, see “Modifying Data Through a View” on MSDN. A view can ensure the novice developer always accesses a set of tables in the most efficient manner.
Take care not to let the number of special case views explode in your database. Although it might be tempting to provide every users a view to meet specific filtering requirements, remember it is possible to SELECT from an existing view and provide additional filtering. A large number of views can be difficult to maintain, especially when breaking changes are required to the underlying schema.
Views As A Security Tool
SQL Server already has the ability to restrict access to column in a table (see a previous article on OdeToCode for more information on this topic). However, a view can be a useful tool to provide both column and row level security for a database. Even though you can deny access to the salary column of a payroll table to user Joe, Joe might be confused when he can see the payroll table but has an error appear when his SELECT statement includes the payroll column. A user-friendly solution is to deny Joe any access to the payroll table, but give Joe access to a view that retrieves data from payroll without salary information.
An even more interesting scenario happens when Joe is a department manager and requires access to the salary column, but only for those employees in his department. Row level security is a perfect fit for a solution with a view. You can construct a view to return only payroll records (with salary information) with Joe’s user name in the row (see the built-in SQL Server function USER_NAME()).
Another great use case for a view is if you need to roll up or aggregate data from a set of tables, as in the following Northwind view.
CREATE VIEW "Sales by Category" AS
SUM("Order Details Extended".ExtendedPrice) AS ProductSales
(Products INNER JOIN
(Orders INNER JOIN "Order Details Extended" ON
Orders.OrderID = "Order Details Extended".OrderID)
ON Products.ProductID = "Order Details Extended".ProductID)
ON Categories.CategoryID = Products.CategoryID
Products.ProductNameThe view above will give us the sum of the total sales broken down by category and product. If we wanted to see just the total sales for Chai, we could use the following query.
SELECT ProductSales FROM [Sales by Category] WHERE ProductName = 'Chai'Queries performing aggregations can be harder to optimize than other queries. Using can a view can not only hide the complexity but can also ensure anyone who needs the aggregation will be using an optimized query.