SQL Server Views
source:
http://odetocode.com/Articles/299.aspx
begin:
SQL Server Views
Posted by scott on 2004年11月28日
An RDBMS uses a view to create a virtual table. The careful use of views can improve the interaction between a .NET application and the underlying data. In this article we will discuss views in Microsoft SQL Server, including best practices for creating and using views.
In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (sometimes even update data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users. Views can also aggregate data (particularly useful if you can take advantage of indexed views), and help partition data. In this article we will look at these different types of view to see when we can take advantage of a view for our application.
Sample View
The sample database Northwind in SQL Server has a number of views installed by default. One example is the “Current Product List” view, shown here.
SELECT
Product_List.ProductID, Product_List.ProductName
FROM
Products AS Product_List
WHERE (Product_List.Discontinued = 0)
From inside an application we can issue the following SQL query to retrieve a set of records representing active products.
SELECT ProductID, ProductName from [Current Product List]
The view has created a new virtual table by using records from the Products table and applying a small piece of logic (a filter on the Discontinued field). You could use the view inside of a query from your application, or a stored procedure, or even from inside another view. Views are a simple but powerful abstraction. You can push query complexity, like filter and join statements, into a view to present a simpler model of the data without sacrificing the database design or integrity.
We often describe a view as a virtual table because the database does not store the view data. Instead, when we retrieve data from a view the database engine recreates the data using the SELECT statements in the view’s definition. Since the database only stores a definition of the view, and not the data, there is no significant cost in space for using a view, although there is an exception to this rule we will discuss later in the article. Note also that the database engines query optimizer can often combine the definition of the view with the SQL queries interacting with the view to provide an efficient query plan (in other words, the database engine might not need to perform the entire SELECT operation in the view if it knows the outer query will filter out additional records).
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
SELECT
"Order Details".OrderID,
"Order Details".ProductID,
Products.ProductName,
"Order Details".UnitPrice,
"Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM
Products
INNER JOIN
"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()).
Aggregate Views
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
SELECT
Categories.CategoryID,
Categories.CategoryName,
Products.ProductName,
SUM("Order Details Extended".ExtendedPrice) AS ProductSales
FROM
Categories
INNER JOIN
(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
GROUP BY
Categories.CategoryID,
Categories.CategoryName,
Products.ProductName
The 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.
With SQL Server 2000 Enterprise Edition you can also receive a huge performance boost by aggregating data with a view and applying an index. The first index to apply to a view is a clustered index. A clustered index will materialize the view. In other words, the index will persist the data in a view to disk. An indexed view can dramatically decrease the amount of time needed to aggregate data as the index already has the sorted, grouped, and calculated results. The drawbacks to watch for include increased disk space, and more overhead on INSERT, UPDATE, and DELETE statements on the view’s base tables, because the database might need to update the index.
Summary
Views are a useful tool to have in your database tool arsenal. More portable than functions, more flexible than stored procedures, view can give your database more secure, more performant, and easier to use.
