Views in SQL Server 2000, Part 4

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

Using views

You can use a view to retrieve data, or update data. A view can be used whenever a table could be used, for retrieving data. For updating data, this may not always be true, and it depends on the view.

Retrieve data

We have already seen several examples of retrieving data through a view. I will now compare two queries, one where we use a view, and one without.

SELECT TOP 1 * 
FROM vwProductSales 
ORDER BY Total DESC

Output:

ProductName	Total
Côte de Blaye	141396.73522949219

SELECT 
        TOP 1 Products.ProductName, 
        SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
FROM [Order Details] AS OD
INNER JOIN Products ON OD.ProductID = Products.ProductID
GROUP BY Products.ProductName
ORDER BY Total DESC

Output:

ProductName	Total
Côte de Blaye	141396.73522949219

Now, which one of these two queries do you find most simple to use? The first one? Yeah, though so!

Update data

There are some restrictions for updating data through a view.

A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.
It’s not possible to update, insert or delete data in a view with a DISTINCT clause.
You cannot update, insert or delete data in a view that is using GROUP BY.
It’s not possible to update, insert or delete data in a view that contains calculated columns.

Also be aware of columns that cannot contain NULL. If it has no default value, and is not referenced in the view, you will not be able to update the column.

Drop views

Deleting a view is not complicated. It’s as easy as:

DROP VIEW vwProductSalesTop10

Alter views

Alter views is as simple as deleting them. We can try to remove the encryption of the view vwProductSalesEncrypted

ALTER VIEW vwProductSalesEncrypted
AS
        SELECT Products.ProductName, SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
        FROM [Order Details] AS OD
        INNER JOIN Products ON OD.ProductID = Products.ProductID
        GROUP BY Products.ProductName
GO

Output:
The command(s) completed successfully.

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