SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Views for Abstraction

By Andy Warren,

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] 
    ,at.[Name] AS [AddressType]
    ,[StateProvinceName] = sp.[Name]
    ,[CountryRegionName] = cr.[Name]
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');


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.

Total article views: 4892 | Views in the last 30 days: 11
Related Articles

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error


Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...


INNER JOIN a database table with XML data

INNER JOIN a database table with XML data


CTE and inner join



Employee job mapping

Soln required for employee and job mapping query

database design