Views in SQL Server 2000

Author: Kristofer Gäfvert
First Published: September 12, 2003
Last Updated: September 12, 2003
Last Reviewed: December 16, 2003
PDF: N/A
Download Code: SQLServerViews-Code.zip

Table of Contents
Introduction
Advantages of using views
Create a view
Create a simple view
With Encryption
With Schemabinding
With View_Metadata
With Check Option
Using views
Retrieve data
Update data
Drop views
Alter views
Partitioned views
Indexed views

Introduction

A view is quite easy to define, but a lot more difficult to create, use and manage. It’s not anymore than a named SELECT statement, or a virtual table. You can select data, alter data, remove data and all other things you can do with a table (with some limitations). But there is a difference between a view and a table. The data accessible through a view is not stored in the database as its own object. It’s stored in the underlying tables that make up the view. The only thing that is stored in the database is the SELECT statement that forms the virtual table (with one exception, indexed views, which will not be covered in this article).

The above picture is an example of a view. It’s based on the Products table and Order Details table of the Northwind Sample Database. What we do is join the two tables together, to produce a virtual table, or a view as we will call it from now on. We get the ProductName from the Products table, and we sum UnitPrice, Quantity and Discount from the Order Details table, to find out how much money every product has given the company. Now, say that we called the view vwProductSales, we could find out the best selling product with this query:

SELECT TOP 1 * 
FROM vwProductSales
ORDER BY Total DESC

Output:
ProductName	Total
Côte de Blaye	141396.73522949219

And remember, nothing is actually stored in vwProductSales, but the statement that joins these two tables together. I haven’t told you yet how to create this view, be patient, but as you can see, views are quite handy.

Advantages of using views

As you could see in the previous section, a view is very useful. Here are some other scenarios when a view can be very useful.

Restrict data access and/or simplify data access
A view can be used to limit the user to only use a few columns in a table. For example if we do not want a user to be able to access all columns because of security. But it could also be because not all columns are interesting for the user. It is also possible to limit access to rows, using a WHERE clause. If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the user use is the view’s name. Simple, but powerful!
Simplify data manipulation
We can also use a view to manipulate data. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved.
Import and export data
A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view.
Merge data
A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator. For example if we had customers in Europe and United States, we could have one server for Europe, and one for United States, with tables that are identical, and then merge all data in a partitioned view. More on this later.

[ 1 ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] [ Next ]