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
