In this stairway level I will be discussing how to use a database VIEW to simplify your Transact-SQL (T-SQL) code. By understanding how to use a VIEW you will be able to better support writing T-SQL code to meet complex business requirements. In this article I will be discussing what a database VIEW is and then providing a number of examples to help you understand how you can use a VIEW to implement different coding scenarios.

What is View?

A view is a virtual table made up of rows and columns. The data can be from a single table or it can come from many tables. You query a view just like you would a normal table. A view is created with the CREATE VIEW statement and is stored within the database in which it is created.

Here are some situations where a VIEW can help with your coding logic:

• You don't want to expose all of the columns of a table to users querying the table.
• Your database schema design is complex so you build views to simplify user access.
• You want to change your database schema design, but want to maintain backwards compatibility so existing code doesn't have to be rewritten.

The best way to gain a better understanding of how to use views is to go through some examples of using views to meet different business requirements.

Sample Data

In order to demonstrate how views work and how they can simplify your T-SQL code I will need some test data for these views to run against. Rather than create my own test data, most of my examples will use the AdventureWorks2008R2 database. If you want to follow along and run my examples in your environment then you can download the AdventureWorks2008R2 database from here: http://msftdbprodsamples.codeplex.com/releases/view/93587

Examples of Simplifying Your SQL Code by Using a View

By using a view you can return list of columns that is a subset of table columns, a set of columns that come from multiple tables, a constrained set of columns based on some WHERE criteria, or a number of other different requirements. In this section I will be providing you a number of different examples of using a view to meeting different business requirements.

For my first example let's assume you have a requirement to not present all of the columns in a single table to an application or adhoc query. For this example let's assume you want to only return non-personal information from the HumanResource.Employee table shown in Listing 1. (Note that this table already existis in the AdventureWorks2008R2 database; the definition is listed here only for reference purposes.)

CREATE TABLE [HumanResources].[Employee](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[ModifiedDate] [datetime] NOT NULL);

Listing 1: Table definition for the HumanResources.Employee table

Non-Personal information that applications and adhoc users require are the following columns: BusinessEntityId, NationalIDNumber, LoginID, OrganizationNode, OrganizationLevel, JobTitle, and HireDate.

In order to create a view that only returns a subset of columns from the HumanResources.Employee table I would use the code in Listing 2.

CREATE VIEW [HumanResources].[EmployeeInfo]
AS
SELECT [BusinessEntityID] 
      ,[NationalIDNumber]  
      ,[LoginID]  
      ,[OrganizationNode]  
      ,[OrganizationLevel]
      ,[JobTitle] 
      ,[HireDate]  
      ,[CurrentFlag]  
    FROM [HumanResources].[Employee];

Listing 2: Script to create view of non-personal information from the HumanResources.Employee table

By reviewing the CREATE VIEW statement in Listing 2 you can see it is very simple. The code for the view is just a simple SELECT statement that contains the columns I want the view to expose in the selection criteria. Once I have created this view I can query it just like a normal table. The script in Listing 3 demonstrates two different SELECT statements that retrieve data from the HumanResources.Employee table using the view I created with the code in Listing 2.

SELECT * FROM [HumanResources].[EmployeeInfo];
SELECT * FROM [HumanResources].[EmployeeInfo]
WHERE JobTitle like '%Manager%';

Listing 3: Two SELECT statements that return data using view

By reviewing the code in Listing 3 you can see that the object referenced after the FROM clause is the name of the view I created in Listing 2. I referenced the view in my SELECT statement just as I would reference a table. The first SELECT statement in Listing 3 returned all the rows in my HumanResources.Employee table, but only returned those non-personal columns in the SELECT clause within my view. The second SELECT statement in Listing 3 demonstrates that I can constrain the rows returned using a WHERE statement, just like I would do when referencing a table.

Sometime your database design is fairly complex, which can complicate building queries to access the needed data in the database. These complex designs might require complex multi-table joins to actually return the data. This is where a view can help. By using a view you can build the complex multi-table join within a view, and then use the view to query the data. By doing this you can simplify the code to query the database, and hide the complexity of a database design within a view. To demonstrate this I've created a view Listing 4 that retrieves sales order data contained in multiple tables.

CREATE VIEW SalesOrderCombined2005
AS
SELECT 
 OH.SalesOrderID
,OH.OrderDate
,OH.ShipDAte
,ST.Name AS TerritoryName
,BTA.City AS BillToCity
,STA.City AS ShiptToCity
,OH.TotalDue
FROM Sales.SalesOrderHeader OH
 JOIN Sales.SalesTerritory ST
 ON OH.TerritoryID = ST.TerritoryID
 JOIN Person.Address BTA
 ON OH.BillToAddressID = BTA.AddressID
 JOIN Person.Address STA 
 ON OH.ShipToAddressID = STA.AddressID 
WHERE YEAR(OH.OrderDate) = 2005;

Listing 4: View that contains multi-table join

The SalesOrderCombined2005 view in Listing 4 joins a number of tables together, and returns only a subset of columns from these tables. Additionally the view has a WHERE clause. The WHERE clause only returns the data if it is related to a sales order that was placed in the year 2005. This view eliminates the need to understand how to join a number of tables together using different key columns. By executing a SELECT statement against the SalesOrderCombined2005 view all those joins are done without you having to reference them in your SELECT statement. By placing complicated join syntax in a view you can simplify the code for retrieving data from a complex database design. Additional these type of views make sure all queries against the database will use the same JOIN syntax. By providing and using a view to query your data you can eliminate the possibilities of JOIN criteria being is written incorrectly.

There are times when you want to evolve your database design over time, but you don't want to break existing code. A view can handle meeting this business requirement. To demonstrate this, review the code in Listing 5.

--- Begin Old Schema 
CREATE TABLE DateDimOld (
ID INT IDENTITY, 
DT DATE, 
DOWTitle varchar(10));
GO
-- Populate DateDimOld
INSERT INTO DateDimOld(DT, DOWTitle) VALUES 
  ('12/1/2013',DATENAME(DW,'12/1/2013')),
  ('12/2/2013',DATENAME(DW,'12/2/2013')),
  ('12/3/2013',DATENAME(DW,'12/3/2013'));
GO
SELECT * FROM DateDimOld;
GO
--- End Old Schema  
--  Begin New Schema  
CREATE TABLE DOWTitle (
DowTitleID INT IDENTITY PRIMARY KEY, 
DOWTitle VARCHAR(10));
GO
CREATE TABLE DateDimNew (
ID INT IDENTITY, 
DT DATE, 
DOWTitleID INT);
GO
ALTER TABLE DateDimNew  WITH CHECK ADD  CONSTRAINT [FK_DateDimNew_DOWTitle_DOWTitleID] FOREIGN KEY(DOWTitleID)
REFERENCES DOWTitle (DOWTitleID)
GO
-- Populate DOWTitle
INSERT INTO DOWTitle (DOWTitle) VALUES 
  (DATENAME(DW,'12/1/2013')),
  (DATENAME(DW,'12/2/2013')),
  (DATENAME(DW,'12/3/2013'));
GO
-- Populate DateDimNew
INSERT INTO DateDimNew (DT,DOWTitleID) VALUES
  ('12/1/2013', 1),
  ('12/2/2013', 2),
  ('12/3/2013', 3);
GO
-- Remove Old Schema
DROP TABLE DateDimOld
GO
-- Create view to similate Old Schema
CREATE VIEW DateDimOld AS
SELECT DDN.ID, DDN.DT, DOWT.DOWTitle 
FROM DateDimNew AS DDN
JOIN DOWTitle AS DOWT
ON DDN.DOWTitleID = DOWT.DowTitleID;
GO
-- Show how VIEW and Simulate Old Schema
SELECT * FROM DateDimOld
-- End New Schema

Listing 5: Old and new schema structure

By reviewing the code in Listing 5 you see that there are two different sections of code. In the first section I defined, populated and displayed some data from an old schema that has a single table named DateDimOld. This table contains both a date column named DT, and a day of the week column named DOWTitle and associates these columns to an ID column. In the second section I defined a new schema to replace the old schema in section one. In this second section I created two tables. The first table is named DOWTitle, which contains the DOWTitle and DOWTitleID columns. The second table is named DateDimNew. This table contains the ID, DT, and DOWTitleID columns. The DOWTitleID column is a foreign key column into the DOWTitle table. This new schema is a normalized schema, whereas the Old Schema is a denormalized schema. In the second section of code I actually drop the table created in the first section of code and create a VIEW with the same name, DateDimOld. The DateDimOld view allowed me to query the new normalized schema just as I would have query the DateDimOld table in the old schema. This new view DateDimOld allowed me to provide backwards compatibility for any code that I might have built that uses the old schema design.

As you can see that are a number of different ways views can be used. In my examples here I only showed you selecting data from a view. Views can also be used to update tables as well. In addition, there are other options that can be use when creating views.

Update Underlying Tables of a View

A view can be used to update data in tables as well. To demonstrate this I will run the code in Listing 6.

INSERT INTO DateDimOld (DOWTitle) 
VALUES (DATENAME(DW,'12/4/2013'));

Listing 6: Inserting data into the underlying tables using a VIEW

The code in Listing 6 doesn't really update the DateDimOld table (which has been deleted anyway), but instead updates the underlying table DOWTitle that is part of the view definition for DateDimOld. After running the INSERT statement in Listing 6 a row was created in the DOWTitle table that contains the value “Wednesday” in the DOWTitle column. Since the DateDimOld is a view of my normalized date dimension tables I will need to also place another row in the table DateDimNew in order for the view DateDimOld to display the “Wednesday” value. To do this I run the code in Listing 7.

INSERT INTO DateDimNew (DT, DOWTitleID) 
   SELECT '12/4/2013', DOWTitleID FROM DOWTitle 
       WHERE DOWTitle = DATENAME(DW,'12/4/2013');

Listing 7: Adding a row to the DateDimNew table

Because the column DOWTitleID wasn't part of the DateDimOld view I wasn't able to use the view to update the DateDimNew table. Instead I had to write the code in Listing 7 to reference the underlying view table directly.

There are some limitations in using views to update the underlying tables of a view. Here are those limitations:

• Only a single underlying table in a view can be updated
• Columns being updated must be directly referenced in the view without any calculations on them
• Columns being modified must not be affected by a GROUP BY, DISTINCT, or HAVING clause
• Your view doesn't contain a TOP clause when the CHECK OPTION (more on this option below) is used

For more information on limitations please refer to the Books Online Documentation.

Making Sure Views Are Not Affected By Other Table Changes or Updates

In the CREATE VIEW statements I have shown you so far, the views created will not limit what you can do to the underlying tables. There are some changes you can make to the underlying tables that a view uses that might break the view, or return unexpected results. One such change that would break a view is to drop a column that a view references. There are situations where you might want to make sure your views are immune to these kinds of problems. When you create a view there are a few additional clauses you can place on the CREATE VIEW or the SELECT statement that will help eliminate these annoying potential problems.

The first thing you can do is bind your view the underlying table schemas. By binding your tables to the underlying schemas you restrict any table changes that might break your view. To demonstrate let me run the code in Listing 8.

ALTER VIEW DateDimOld WITH SCHEMABINDING AS 
SELECT DDN.ID, DDN.DT, DOWT.DOWTitle 
FROM dbo.DateDimNew AS DDN
JOIN dbo.DOWTitle AS DOWT
ON DDN.DOWTitleID = DOWT.DowTitleID;
GO

Listing 8: Create a view with schema binding

In Listing 8 I dropped and recreated the DateDimOld view. When I recreated it I added the WITH SCHEMABINDING clause. This created a schema bound view. When I made that change I also had to modify the SELECT statement in the view slightly. The change I made was to have two part names for all tables. It is recommended that you always use two part naming when referencing SQL Server tables regardless of whether SQL Server technically requires it or not. This requirement meant I had to add “dbo.” in front of the two table names in my original view. Other than that, this view is exactly as it was originally.

To show how schema binding limits what I can do to the underlying table let me run the code in Listing 9.

ALTER TABLE dbo.DateDimNew 
  ALTER COLUMN DT INT;

Listing 9: Trying to alter a table with schema binding

When the code in listing 9 is run I get the errors displayed in Report 1.

Msg 5074, Level 16, State 1, Line 1
The object 'DateDimOld' is dependent on column 'DT'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN DT failed because one or more objects access this column.

Report 1: Errors received when altering a column of a schema bound view

By reviewing the output in Report 1 you can see the database engine kept me from modifying the DT column, which is included in the view definition. By creating a schema bound view I have made sure someone doesn't come along and modify any part of the tables that might affect my DateDimOld view.

Another option available with creating a view is the WITH CHECK OPTION. The WITH CHECK option allows you to put constraints on your view to make sure any update made to the underlying tables are selectable using a view. To show you how the WITH CHECK OPTION is used review the code in Listing 10.

CREATE TABLE DayOfTheWeek(DayOfTheWeek varchar (10), 
              DayOfTheWeekNum int);
INSERT INTO  DayOfTheWeek VALUES
    ('Monday',0),
    ('Tuesday',1),
    ('Wednesday',2),
    ('Thursday',3),
    ('Friday',4);
GO
CREATE VIEW DisplayDayOfTheWeek 
AS 
SELECT DayOfTheWeek, DayOfTheWeekNum FROM DayOfTheWeek
WHERE DayOfTheWeekNum < 5
WITH CHECK OPTION;

Listing 10: Creating view that has WITH CHECK OPTION

In the code in Listing 10 you can see I created a table and populated a table named DayOfTheWeek. I also created a VIEW named DisplayDayOfTheWeek that constrains the days returned by using a WHERE clause, and I added the WITH CHECK OPTION. By adding the WITH CHECK OPTION, SQL Server will not allow me to INSERT or UPDATE a row using the DisplayDayOfTheWeek view unless the DayOfTheWeekNum value is less than 5. To test this out I can run the code in listing 11.

INSERT INTO  DisplayDayOfTheWeek VALUES
    ('Saturday',5);
UPDATE DisplayDayOfTheWeek
SET DayOfTheWeekNum = 5
WHERE DayOfTheWeek = 'Friday';

Listing 11: Code to test the WITH CHECK OPTION

When the code in Listing 11 tries to INSERT a new row with a value greater than 5, or update my existing Friday row to a DayOfTheWeekNum value greater than 5, I get the error shown in Report 2. In fact, the code in Listing 11 will generate this message twice, once for the INSERT and once for the UPDATE.

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Report 2: Code to test the WITH CHECK OPTION

By reviewing the message you can see the WITH CHECK OPTION caused my INSERT and UPDATE statement in Listing 11 to fail. If you want to actually INSERT or UPDATE these rows you have two options. One options would be to remove the WITH CHECK OPTION. Thi s would allow you to change the underlying table through the view, but selecting from the view would still not show those values that meet the conditions in the view definition. If you want insert and update those rows and have them displayed by the view then the second option would be to change the WHERE condition in the view to allow the new values to be selected. (Keep in mind that the WITH CHECK OPTION only applies to changes made through the view; is does not prevent updates or inserts made directly to the underlying table.

If you want to control the type of statements that might impact your view then you should consider using SCHEMA BINDING and/or the WITH CHECK OPTION.

Performance Considerations When Using Views

Are there performance problems with using views? As with most SQL Server questions the answer is “it depends”.

Performance of a view will depend on what the view is doing. A simple view that reads a single table with no JOIN clauses will most likely perform very similarly to a query that just references a single table. But what if you have a view that references a view that references a view, and these views contain multiple JOIN clauses? The underlying query that actually gets executed by your simple SELECT statement that references a view might explode out into a very complex SELECT statement with multiple JOIN clauses and might end up doing a lot more work than you are expecting.

Something else worth mentioning about performance problems with views is when a view contains a number of tables joined together but you only want to return data from a single table in the view. In this case SQL Server will still have to join all the tables in the view to return the data from a single table. This can lead to extra work for SQL Server to join all those tables in the view and slower response time for those queries that only want to return data from a single table in the view. If you find you are only going to return data from a single table in a view and performance is important then you would be better off writing your query to go against the single table instead of using a view that contains joins of multiple tables.

CVews are a great way to simplify code and hide the complexity of the database schema. But hiding this complexity can cause serious performance problems. If you plan to use views make sure you know what the view is doing behind the scenes. Understanding the work that the query engine will have to do to execute queries against your views will help you develop code that performs well.

Securing Data using a view

Another reason people use a view is to secure access to some columns in a table. Suppose you have a business requirement to allow users to do reporting against tables that contains confidential data, like a social security number, or credit card number. You might not want them to have access to these confidential columns. One way to make sure they can't read these confidential data columns is to create a view of the table that excludes those confidential columns, and don't provide the users SELECT rights on the underlying table.

Summary

Views are a great way to implement security, simplify querying complex database schema and/or providing backwards capabilities. But there is an evil side of views if you start nesting views without understanding the performance impact this might cause. As you're looking at a given business need that requires a T-SQL solution, consider a view as just one of many tools you might be able to use to implement your solution.

Question and Answer

In this section you can review how well you have comprehended using views for querying your database by answer the following questions.

Question 1:

What are good business requirements that a view can help you implement?

• A need to keep applications or ad-hoc queries from having access the underlying columns in a table.
• A need to simplify the code required to query a complex database structure.
• A need to provide backwards compatibility.
• All of the above

Question 2:

You need to make sure that when a column value is updated or inserted via a view that it is selectable via the view. Which clause provides this functionality?

• CREATE VIEW
• WITH SCHEMABINDING
• WITH CHECK OPTION
• None of the above

Question 3:

You need to restrict access to confidential data in a table. What method could a view be used to restrict access to this data?

• Create a view using the WITH CHECK OPTION
• Create a view that uses the WITH SCHEMABINDING option
• Create a view that excludes the confidential columns in a table and do not proved SELECT access to the table.
• Create a view that excludes the confidential columns in a table and proves SELECT access to the table.

Answers:

Question 1:

The answer is d. There are lots of reasons for using a view over a direct query. a, b, and c are some of those reasons.

Question 2:

The correct answer is c. The CREATE VIEW is not a clause that provides any additional data integrity checks. The WITH SCHEMABINDING clause makes sure any ALTER TABLE statement doesn't cause a problem with the view when an underlying table structure of a view is changed. It is the WITH CHECK OPTION that makes sure you can't update the underlying tables unless the changes are immediately query-able using the view.

Question 3:

The correct answer is c. Answers a and b don't specifically restrict access to confidential columns because they do not mention excluding the confidential columns from the view. Answer d is incorrect because if people have access to the underlying table that contains confidential data then they can still select the confidential columns by writing a query that goes directly against the table.