Lesson 12. Working with SQL Server Views
A view is a saved SELECT statement. A view can retrieve data from one or more tables. After you create a view, you can select data from it just as you can select it from a table. In this lesson, you learn:
What views are and why you would want to use them
How to use the SQL Server Management Studio Query Builder to create a view
How to use T-SQL to create or modify a view
An Introduction to Views
Views can only select data. They cannot update it (although you can update the data in the result of a view). For example, a T-SQL UPDATE statement in a stored procedure updates data. Although you cannot use a T-SQL UPDATE statement in a view, you can update the results returned from a SELECT statement.
Views have several advantages. They enable you to
Join data so that users can work with it easily.
Aggregate data so that users can work with it easily.
Customize data to users’ needs.
Hide underlying column names from users.
Limit the columns and rows with which a user works.
Easily secure data.
Although a normalized database is easy to work with and maintain from a programmer’s viewpoint, it is not always easy for the user to work with. For example, if users look at the HumanResources.Employee table, they see only the BusinessEntityID associated with the employee. If they want to see the employee’s name, they must join the HumanResources.Employee table with the Person.Person table. This is not a particularly easy task for users to accomplish. Using a view, you can join the HumanResources.Employee table and the Person.Person table. You provide the view to the user. The user can build forms, queries, and reports that are based on the view without having to understand how to join the underlying tables.
Just as a view can join data, it can also aggregate data. You can very easily create a view that contains the total order amounts for each customer. The user can use the view as the foundation for forms, queries, and reports they build. Once again, it is not necessary for the user to understand the syntax required to aggregate the data.
Another advantage of views is their capability to customize data to the users’ needs. For example, a column in a view can combine the first name and last name of a customer, or it can combine the customer’s city, state, and ZIP Code. Users do not need to understand how to combine this information. Instead, they use the view as the foundation for the forms, queries, and reports they need.
Developers often use column names that are not particularly intuitive for users. This is another situation where views come to the rescue. You can easily build a view that aliases column names. Users will never see the underlying column names. You simply provide them with access to the view, and they can easily build forms, queries, and reports.
The number of fields in a table can be overwhelming to users. Most of the time, there are certain fields that users need for the majority of the work they do. You can create views containing only the critical fields, which simplifies the process when users build forms, queries, and reports based on the table data.
A major advantage of views is the security they provide. You can grant logins and roles the rights to views without granting those logins and roles rights to the underlying tables. An example is an employee table. You can create a view that includes the EmployeeID, FirstName, LastName, Extension, and other nonsensitive fields. You can then grant rights to the view. Although the users have no rights to the Employee table and therefore no access to fields such as the employee salary, they gain rights to the rows and columns included in the view.
SQL Server views are very powerful. Using user-defined functions, you can parameterize views. Using the TOP syntax, you can order view results. All these features make SQL Server views extremely powerful!
Creating a Simple View
A view is actually a SELECT statement with a CREATE VIEW statement that causes SQL Server to save it as a view. You can use a few different methods to create a SQL Server view. This lesson discusses the following methods:
You can use the Microsoft SQL Server Management Studio Query Builder to create a view.
You can use T-SQL to create a view.
The sections that follow cover each of these options.
Using the Microsoft SQL Server Management Studio Query Builder to Create a View
The Management Studio Query Builder facilitates the process of creating a view. To create a view, follow these steps:
1. Right-click the Views node of the database in which you want the view to appear and select New View. The Add Table dialog box appears (see Figure 12.1).
FIGURE 12.1 The Add Table dialog enables you to add tables, views, functions, and synonyms to your view.
2. Click each table, view, function, and synonym you want to add to the view and click Add. In the example shown in Figure 12.2, the Sales.Customer and Sales.SalesOrderHeader tables are included in the view.
3. Click Close when you have finished adding objects to the view. Your screen should appear as in Figure 12.3.
4. Click the check boxes to the left of the field names to select the fields you want to add to the view. If you prefer, you can drag and drop fields to the column list on the query grid. Figure 12.4 shows a view with the CustomerID, AccountNumber, SalesOrderID, OrderDate, and SalesOrderNumber fields included.
5. Specify any criteria that you want to apply to the view. To add criteria, enter the desired criteria in the Criteria column of the appropriate field on the query grid. Adding criteria limits the records returned when you execute the view. Figure 12.5 shows criteria limiting the selected records to those with order date values between 6/1/14 and 6/30/14.
6. Test the view using the Run button. The output should appear as in Figure 12.6.
7. Attempt to close the view. SQL Server prompts you to save changes to the view.
8. The view appears in the list of views under the Views node. You can treat it much like a table.
Note
The views you create do not automatically appear on the Views node. To make them appear, right-click the Views node and select Refresh.
The view builder offers four panes: the diagram pane, the grid pane, the SQL pane, and the results pane. The diagram pane shows you the tables included in the view. The grid pane graphically presents you with the columns, aliases, tables, groupings, and criteria for the data in the view. The SQL pane shows you the actual SQL statement that underlies the view. The results pane provides you with the results of executing the view. You can easily hide and show each of these panes with the Show/Hide Diagram Pane tool, Show/Hide Grid Pane tool, Show/Hide SQL Pane tool, and Show/Hide Results Pane tool, respectively.
To use SQL Server Management Studio to modify a view:
1. Expand the Views node for the database until you can see the view you want to modify.
2. Right-click the view you want to modify and select modify. The view appears as in Figure 12.7.
3. Make the desired changes and then close and save the view.
Tip
You can easily drag and drop tables to the diagram pane of the view. Simply resize the View and Object Explorer windows so that you can see both windows simultaneously and then drag and drop the tables from the Tables node of the appropriate database to the diagram pane of the view.
Using T-SQL to Create or Modify a View
In addition to using the Management Studio View Builder to build a view, you can use T-SQL to create a view. Rather than building the view graphically, as outlined in the preceding section, you type the entire CREATE VIEW statement from scratch. The syntax for a CREATE VIEW statement is as follows:
CREATE VIEW [DatabaseName] [<owner>] ViewName
[(column [,...n])]
[WITH <ViewAttribute> [,...n]]
AS
SelectStatement
[WITH CHECK OPTION]
<ViewAttribute> :: = [ENTCRYPTION|SCHEMASBINDING|VIEW_METADATA]
An example of a CREATE VIEW statement is the following:
CREATE VIEW vwUSACustomers
AS
SELECT CustomerID, FirstName, LastName, City
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'United States'
The statement creates a view named vwUSACustomers, which selects the contents of the BusinessEntityID, FirstName, LastName, and City fields from the Sales.vIndividualCustomer view for all customers in the USA.
To type and execute the CREATE VIEW statement, follow these steps:
1. Click the New Query button on the toolbar. Your screen appears as in Figure 12.8.
2. Type the SQL statement into the available pane.
3. Click the Execute button on the toolbar. The results should appear as in Figure 12.9.
If you want use T-SQL to modify a view, you must use an ALTER VIEW statement rather than a CREATE VIEW statement. An example of an ALTER VIEW statement is shown here:
ALTER VIEW vwUSACustomers
AS
SELECT CustomerID, FirstName, LastName, City
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia'
This example modifies the vwUSACustomers view, changing the criteria to return all the customers in Australia.
Summary
Views are a critical part of any application you build. It is therefore important you understand how to create and work with views. In this lesson, you learned how to create and modify simple views, using both the SQL Server Management Studio and T-SQL.
A. Views enable you to join data, aggregate data, customize data to the user’s needs, hide underlying column names from users, limit the columns and rows a user works with, and easily secure data.
Q. Explain why you would want to join data so that users can easily work with it.
A. A normalized database is not always easy for the user to work with. For example, only the CustomerID is stored in the Orders table. The Company Name is stored in the Customers table. To see both the Customer and Order information, the user must join the tables. Using a view, you can join the tables for the user so that he or she can work with the view as if it were a single table.
Q. Explain what it means to customize data to a user’s needs.
A. Using a view, you can create a column that combines first, middle, and last name of an employee or the city, state, and ZIP Code from an address. This makes it much easier for the user to work with this data.
Q. How do views help you to secure data?
A. You can grant rights to logins and roles to the views you create. It is not necessary to grant rights to the underlying tables. In this way, you can give users access to just desired columns and rows. For example, you can give users rights to name and address data for the sales department in the employee table.
Workshop
Quiz
1. Name two ways you can create a view.
2. What statement do you use to create a view?
3. The result of a view is updateable (true/false).
4. Name the four panes of the view builder.
5. You cannot drag and drop tables onto the diagram pane of the view (true/false).
6. What statement do you use to modify a view?
Quiz Answers
1. Using SQL Server Management Studio and using T-SQL.
2. CREATE VIEW.
3. True.
4. Diagram, grid, SQL, and results.
5. False. You can drag and drop from the Tables node of the appropriate databases to the diagram pane of the view.
6. ALTER VIEW.
Activities
Create a view by using the Microsoft SQL Server Management Studio Query Builder. Include BusinessEntityID, JobTitle, BirthDate, Gender, HireDate, VacationHours, and SickLeaveHours from the HumanResources.Employee table. Sort the result in ascending order and return just the rows where the HireDate is between 1/1/2013 and 12/31/2013. Close and save the view as vw2013Hires. Modify the view and add the LoginID to the view. Create another view by using T-SQL. Include the BusinessEntityID, SalesQuota, Bonus, SalesYTD, and SalesLastYear from the Sales.SalesPerson table. Sort in descending order by SalesYTD and return just the rows where the SalesYTD is greater than 2 million. Close and save the view as vwBigHitters. Modify the view (using T-SQL) and add the CommissionPct field to the view. Change the criteria for the SalesYTD to 3 million.