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.
