Server begin:
SQL Server views
Posted by Scott on November 28, 2004
An RDBMS uses a view to create a virtual table. The careful use the views can improve the interaction between a. NET application and the underlying data. In this article we'll discuss views into 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 your 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. can use views to join two tables in your database and present the underlying data as if the data were coming from a Si Ngle table, thus simplifying the schema of your database for users performing Ad-hoc. can also use views as a security mechanism to restrict the data available to end users. Views can also aggregate data (particularly useful if can take advantage to indexed views), and help partition data. In this article we'll look at the different types of view to? When we can take advantage of a view for our Applicat Ion.
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 a application we can issue the following SQL query to retrieve a set of records active products.
SELECT ProductID, ProductName from [current Product List]
The view has created a new virtual table by using records to the Products table and applying a small piece of logic (a F Ilter on the Discontinued field). You are could use the view inside of a query from your application, or a stored procedure, or even to inside view. Views are a simple but powerful abstraction. Can push query complexity, like filter and join statements, in 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 A view, although there is a exception to this rule we'll discuss later in the article. Note also this database engines query optimizer can often combine the definition of the view with the SQL queries inte Racting with the "view to provide a efficient query plan" (in other words, the database engine might not need to perform th E entire SELECT operation in the view if it knows the outer query would filter out additional records).
When to use A View
You are need to have a goal in mind when creating a view. There are a number of scenarios where you'll want to look for a view as a solution.
To hide is 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 the data for performance.
Let's take a look at all of these scenarios.
Complexity and customization
Taking care of complex joins and filtering rules inside of a view can benefit other users. As a 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 A Ad-hoc Reporting tool can take advantage of the above view in building customized to reports RT her goals. She can use the "view to" all of the details about a order without finding the "tables to join" for product and order INF Ormation, 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 T
