SQL Server Views

So far we have been discussing database management, backup, transactions, log shipping etc. In most places developers are generally not concerned with these tasks; but they fall under the domain of DBAs.  We will now turn our attention to the database development, primarily the tasks that are in the domain of a developer. Although admittedly, today's topic could go either way.

In this post, we will take a look at views and how you can create views to simplify some of the complex operations. Views are logical representation of your schema. They hide the underlying complexities of your database schema, table relationships etc.

For example, below is a subset of tables from my database with related tables tied together via referential integrity.

Let's assume you want to run the following query...

SELECT C.FirstName,C.LastName,Count(O.OrderID)As TotalOrders,S.FirstName,S.LastName
FROM Customers C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName

Even though this query only joins three tables, it is quite complex. What if you can create a view that abstracts all this information and your application can simply use the view to get the same results instead of using this query.

CREATE VIEW view_Orders

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers 
C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName
GO

Now, I can simply write a query against this view which will return me same results as the query above

SELECT * FROM view_Orders

In many ways, views are pseudo-tables. You can open a view in design mode and it will show you the tables that are used in that particular view and relationship between them. For example, if I right click on the view I created above and select design, I will see something like this, showing three tables by their Alias name. Also notice that the columns this view is returning are checked and the column which is being computed (OrderID) has a function sign on right hand side.

While a view allows you to use many clauses available with SELECT such as GROUP BY, TOP and functions such as Count, Sum etc. you can't use ORDER BY Clause, unless you use TOP. For example, If I change my above view to something like this...

CREATE VIEW view_Orders

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers C 
INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName 
ORDER BY C.FirstName
GO

I will get an error. But I can use ORDER BY Clause if I want to return TOP x number of rows like this...

CREATE VIEW view_Orders

AS
SELECT TOP 10 C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers C 
INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName 
ORDER BY C.FirstName
GO

If you want to order result set returned by a view, you can always supply ORDER BY clause in your query

CREATE VIEW view_Orders ORDER BY CustomerFirstName

Views also allow you to add certain options when creating them. The two most important once are WITH SCHEMABINDING and WITH ENCRYPTION.

If you create a view with SchemaBinding, you will not be able to alter the schema of any tables that are associated with this view without first dropping the view  or altering the view, removing SchemaBinding option. For example, above view can be created as

CREATE VIEW view_Orders WITH SCHEMABINDING

Also, you cannot use SchemaBinding unless you use the two part table name i.e. schema name.table name. In my previous example, all tables have dbo schema, so I will modify the above statement like this...

CREATE VIEW view_OrdersWITH SCHEMABINDING

AS
SELECT TOP 10 C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,S.LastName As SalesLastName
FROM dbo.Customers C INNER JOIN dbo.Orders O ON C.CustomerID=O.CustomerID
INNER JOIN dbo.SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName ORDER BY C.FirstName
GO

You can also Encrypt view by using WITH ENCRYPTION. When a view is encrypted, you cannot see it either in design mode or generate a create  / alter / drop and create script. The only script you can create is Drop.  An encrypted view cannot be un-encrypted.

CREATE VIEW view_OrdersWITH SCHEMABINDING, ENCRYPTION

AS
SELECT TOP 10 C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,S.LastName As SalesLastName
FROM dbo.Customers C INNER JOIN dbo.Orders O ON C.CustomerID=O.CustomerID
INNER JOIN dbo.SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName ORDER BY C.FirstName
GO

Partitioned Views
A partitioned view is defined as a view that selects data from multiple tables that have the same structure. The data is said to be partitioned, hence the term partitioned. The data is combined together using UNION ALL. The two tables can be on the same server or even on different servers.

Views are powerful feature in the sense that they abstract away some of the underlying complexities and simplify working with data.

Although views are generally used for selecting data, you can use them to update/insert data, but you are limited in what you can do when inserting/updating data via views. In next post we will discuss using views to insert/update data.

Thank you