I think most of us understand that views serve as an abstraction layer, but

I'm often surprised that many of us think of them as only useful for abstracting

complicated joins. There are some other scenarios where views prove to be

incredibly useful if you have thought about the possibilities!

Here's an example from AdventureWorks that shows the basic syntax of a view

and how we're joining up a bunch of tables to make it easier them easier to work

with.

CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
GO

One common question about views is "what operations if any can I perform on

it?. The answer, sadly, is 'it depends', and that is because it really depends

on the statement you wrote that defines the view. For example, if I try to

delete one row from the above view, I get the following:

set rowcount 1 --for this test only
delete from sales.vIndividualCustomer

set rowcount 0 --reset from testing

An update though, will succeed:

update sales.vindividualcustomer set lastname='Earp' where customerid=11016

An insert will fail:

insert into sales.vindividualcustomer default values

Even an update would fail if the view contained a group by or distinct. We can

sometimes cheat a bit by adding an instead of trigger to a view. Yes, it's

possible, just not frequently done. It's an interesting technique to apply. What

we know so far is that we can always select from a view (assuming appropriate

permissions), but inserts, updates, and deletes may or may not succeed depending

on the view definition. I didn't demonstrate above, but truncates also fail on

views.

With that background in mind, the first way I use views that doesn't really have

to do with hiding joins/complexity, but instead has to do with referencing another database

or server. For

example, let's say that I have an Orders database and an Corporate database, and

I have some queries in Orders that need to pull information from the Employees

table in Corporate. It's very common to see a view something like this:

create view vOrders as
select * from dbo.OrderDetails OD inner join Corporate.dbo.Employees E on OD.EmployeeID = E.EmployeeID

Perfectly valid to do this, but it means you have embedded dependencies on

another database. As a DBA, I want to be able to move any database to another

server at any time and only have to have connection strings changed, not have to

go through an entire test cycle. A cleaner way to do this would be to create two

views:

create view Employees as
select * Corporate.dbo.Employees
create view vOrders as
select * from dbo.OrderDetails OD inner join Employees E on OD.EmployeeID = E.EmployeeID

I still have the dependency, but it's isolated in one place. I can have hundreds

of references to the Employees table now and only have to change it in only

place. A variation of the technique is to reference a linked server. In this

example, I'm now getting the Employees table from a different location:

create view Employees as
select * Server14.Corporate.dbo.Employees

You might even consider a special naming convention for these views, perhaps

EXT_Employees or LINK_Employees, to make it easy to identify them. The rule as a

DBA should be that any reference to another server or database should be

encapsulated in a stand alone view (or synonym if you prefer in SQL 2005).

Another scenario where I use views is when I want to make some type of change to

a table and hide that change from the users. For example, maybe I have an off

the shelf application that fails if I add a column to the Employees table. One

way to solve this is to rename the Employees table to EmployeesOriginal, add the

column, then create a view called Employees that only returns the original set

of columns from the renamed table. I could use this same idea to move the table

to a different database or server if needed, perhaps for for space or

performance reasons.

A variation of that technique can be used to appear to speed up the

loading of data. Let's suppose that each day we truncate and bulk load one

million rows into a table called OrderHistory, and that operation takes about 5

minutes. That's 5 minutes where the users won't be able to run queries that

reference that table. Five minutes isn't bad, but imagine it creases to hours,

then we have a problem. We can reduce the down time by creating OrderHistoryA

and OrderHistoryB, and then creating a view called OrderHistory that will

initially be 'select * from orderhistoryA'. We can then truncate and reload

orderhistoryB and when done, we just alter our OrderHistory view to have the

definition of 'select * from orderhistoryB' and then issue an sp_refreshview to

make the change live. We haven't changed the physical work being done, but to

the user there is essentially no down time.

It's possible to over use the view as an abstraction technique. I think

developers often appreciate abstraction more than DBA's because it is so common

in programming, but it can lead to views written on views written on views. The

principle is sound, but in practice the resulting query plans range from ok to

abysmal. It's another place where the best answer is 'it depends'!

Think about abstractions and how it can make things easier, and make sure your

team understands the possibilities, it may pay off in the most unexpected ways.