Home > Articles > Data > SQL Server

Creating and Optimizing Views in SQL Server

Views are saved SELECT statements that allow you to operate on the results that are returned from them. They can be used to provide row- or column-level access to data, to wrap up complex joins, to perform complex aggregate queries, and to otherwise customize the display of data. Views are also a powerful security tool. In this chapter, learn how to define, create, and modify views, and how to perform index analysis and optimize performance in SQL Server.
This chapter was selected from Microsoft® Access Developer's Guide to SQL Server.

In This Chapter

  • Defining Views

  • Creating and Modifying Views

  • Using Views to Update Data

  • Indexed Views

  • Optimizing Views and Queries

Defining Views

Views are often puzzling to Access developers. It can take a while to understand exactly what views can and cannot do. Think of views as saved SELECT statements, with just a couple of limitations. The biggest problem is that just when you get used to accepting a limitation, Microsoft comes up with an amazing, new feature that overcomes it! It's great to get the extra features, but it makes it harder to nail down exactly what you can and cannot do with views.

Views have never in the past been able to contain parameters; however, as shown later in the chapter, user-defined functions can now be used like views, effectively allowing you to create parameterized views that return different results depending on the parameter values that get passed in each time the function is run. Unlike stored procedures (which also support parameters), these parameterized functions can be updateable, as you'll see later in this chapter.

The SQL-92 standard also mandates that views cannot be sorted with an ORDER BY clause. Like tables in a relational database, the order of the records in a view is undefined. However, Transact-SQL includes an extension to the language that allows you to select TOP values with a view; to support that, it also supports sorting. To return the top 10 customers or the top 10% of customers, based on sales over the past year, SQL Server needs to sort customers by sales. So, a view that returns the top 100% by sales would essentially be a sorted view. The TOP syntax is covered in Chapter 8, "Introduction to Transact-SQL (T-SQL)."

Views have also never been able to contain triggers. In SQL Server 2000, however, a view can contain one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). If any of these actions is performed on a view, SQL Server calls the INSTEAD OF trigger "instead of" making any direct changes to the data. INSTEAD OF triggers in views are covered later in this chapter.

Another limitation of views that you may be familiar with is that a view cannot be indexed. Guess what? In SQL Server 2000, you can indeed create indexed views, as shown later in this chapter.

Views still, however, don't allow you to perform any of the other major SQL actions besides selecting—views can't contain INSERT, UPDATE, or DELETE statements or DDL (Data Definition Language).

View Syntax

View syntax is very simple—it's nothing more than a SELECT statement with a CREATE VIEW...AS wrapper that causes the statement to be saved as a view. Listing 9.1 shows a simple view that selects certain columns from the Customers table in the Northwind database.

Listing 9.1 A Simple View

CREATE VIEW vwCustomerAddressList
AS
SELECT CompanyName, ContactName, Address,
 City, Region, PostalCode, Country

FROM Customers

Later in the chapter, the special options WITH ENCRYPTION and WITH CHECK OPTION are discussed. These can be added to the basic SELECT syntax when defining a view.

What Views Are Good For

The primary use of views is to present data to users of an application. Views can be used in place of tables in your Access front-end application, whether you're using an Access database or an Access project. Views can also reference functions and other views.

Join Tables

A user can query a single view instead of having to learn complex join syntax and understand the structure of your database. As far as the user is concerned, the view looks just like a table. The following Transact-SQL statement selects all the data for the view shown in Listing 9.1, sorting the result set on the CompanyName column:

SELECT * from vwCustomerAddressList
ORDER BY CompanyName

In this case, the underlying view didn't contain any joins. But even if it had, the SELECT statement wouldn't have to change. The information about where the data in a view comes from and how it is pulled together from various tables is all encapsulated inside the view.

Customize Data

Tables can be customized and tailored to the needs of the users by way of views. For example, you could create a view for a salesperson that only displays her own orders, or you might create a view for the manager, who is only interested in seeing the total sales for each salesperson. Views give you a mechanism for presenting the same data to different users in different ways.

Restricting Columns and Rows

Columns and rows can be restricted, allowing users to focus on specific data that interests them. Unnecessary data is left out. This also helps network performance because less data is traveling over the wire to client applications. Using a view to filter data on the server is much more efficient than fetching an entire table and then applying a filter on the client. To restrict the rows in a view, you use the same kinds of criteria in a WHERE clause that you are already familiar with. To restrict the columns, you simply leave certain columns out of the SELECT clause. For example, the following view limits the result set by selecting only the employee first name, last name, and hire date columns from the Employees table, and it limits the rows returned to employees hired this year:

CREATE VIEW vwEmployeesHiredThisYear
AS
 SELECT LastName, FirstName, HireDate
 FROM Employees
 WHERE Year(HireDate) = Year(Getdate())

Preaggregate Data

One of the most powerful uses of views is for reporting. A view can be used to wrap up complex queries containing subqueries, outer joins, and aggregation. This simplifies access to the data because the underlying query does not have to be written on the client and then submitted each time a report is run.

You may already have discovered that you can often greatly speed up Access reports by first saving the results of a complex record source query into a temporary table and then basing the report on that table rather than on the query. Similar performance benefits can be gained with SQL Server by basing your reports on views. For example, the following view selects a customer's total orders and contains joins between the Customers table, the Orders table, and the Order Details table. When a user selects data from the view, only the result set is passed over the network—all of the joins and aggregations are performed on the server, not on the client.

CREATE VIEW vwCustomerOrders
AS 
SELECT dbo.Customers.CompanyName, 
 SUM(dbo.[Order Details].UnitPrice * 
 dbo.[Order Details].Quantity) AS Total
FROM dbo.Customers 
 INNER JOIN
 dbo.Orders 
 ON 
 dbo.Customers.CustomerID = dbo.Orders.CustomerID 
 INNER JOIN
 dbo.[Order Details] 
 ON 
 dbo.Orders.OrderID = dbo.[Order Details].OrderID
GROUP BY dbo.Customers.CompanyName

Rules for Creating and Using Views

Views are created inside individual databases, however, views can reference tables and views in other databases or even on other servers (if the view is defined using distributed queries). Here are some general guidelines for creating views:

  • Names of views must follow the same rules for identifiers, discussed in Chapter 7, "Designing and Creating a SQL Server Database," and they must be unique for each owner in the database. However, your best bet is to make sure that all objects are owned by dbo so that there are no breaks in the ownership chain. Later in this chapter, views, security, and ownership chains are discussed.

  • Views can be built on other views and can be nested up to 32 levels deep.

  • To create a view, you must be granted permission to do so by the database owner, and you must have appropriate permissions on any tables or views referenced in the view definition.

Things You Can't Do in Views

Views are much more restricted in their capabilities than Access queries. Here's a list of the things you can't do in a view:

  • Rules, defaults, and triggers cannot be associated with views. An exception to this is INSTEAD OF triggers, which are new to SQL Server 2000 and are discussed later in this chapter.

  • ORDER BY, COMPUTE, and COMPUTE BY clauses or the INTO keyword can't be used in views. An exception to this is the ORDER BY clause, which is allowed only in conjunction with the TOP keyword. ORDER BY and TOP are covered later in this chapter.

  • Full-text indexes can't be defined on views. However, other types of indexes can be defined on views in SQL Server 2000. (This feature is not available in SQL Server 7.0.)

  • Views cannot be based on temporary tables.

  • You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.

  • You can't pass parameters to views, although you'll learn later in the chapter how user-defined functions in SQL Server 2000 enable you to skirt this restriction.

Views and Security

Views are frequently used as security mechanisms to prevent users from having direct access to tables while still allowing them to work with data. All permissions can be removed from the underlying tables, and as long as the owner of the table is the same as the owner of the view, the user will be able to interact with the data based on the permissions granted to the view. Figure 9.1 shows the Permissions dialog box for vwCustomeraddressList. As long as both the view and the Customers table are owned by dbo, the permissions granted on the view will take precedence over permissions removed from the Customers table.

Figure 9.1
Setting permissions on a view.

Because views allow you to restrict the rows and columns that are available, using them as a security tool is very convenient. For example, you could create two views on an Employees table, one with a sensitive salary column included that only managers had permissions on, and a second view with that column omitted for everyone else.

Chain of Ownership Issues

One reason it's recommended that the dbo own all objects in a database is that problems arise when the chain of ownership is broken. This happens when the view and the underlying table do not have the same owner. When the chain of ownership is broken, you need to grant users the same permissions on the underlying tables as you want them to have on the view, which nullifies the effectiveness of views as security mechanisms. Another problem you'll encounter with Access projects (ADPs) is that if you want users to be able to update data using a view, you also have to grant them permissions on the underlying tables.

Using Views to Implement Column-Level or Row-Level Security

Although you can set column-level security in SQL Server, it is tricky to maintain because it's pretty well buried in the table properties. Row-level security must be defined at runtime because it's impossible to know what values a table will contain until then. A simple solution to the issue of implementing row-level and column-level security is to revoke all permissions to the underlying tables. Instead, define views with only selected columns to implement column-level security and define views with a WHERE clause to implement row-level security. Then grant appropriate permissions on the views for the users and roles you want to be able to access the data.

Hiding Underlying Table or Column Names

Views can also be used to hide the real column names or underlying schema of your tables. This can be implemented using the AS keyword to alias columns in your SELECT statements:

CREATE VIEW vwObscure
AS
SELECT CompanyName AS C, ContactName AS CN
FROM Customers

Anyone using the view would query it using the following syntax and would never know which actual tables or columns were being used:

SELECT C, CN
FROM vwObscure

A second technique is to use a column list:

CREATE VIEW vwObscure (C, CN)
AS
SELECT CompanyName, ContactName
FROM Customers

The following query will also work with either view:

SELECT * FROM vwObscure

Encrypting Views

The definition of a sensitive view can be encrypted to ensure that its definition cannot be obtained by anyone, including the owner of the view, by using the WITH ENCRYPTION option when the view is created. Listing 9.2 shows the syntax for creating an encrypted view.

Listing 9.2 Creating an Encrypted View

CREATE VIEW vwEncrypted
WITH ENCRYPTION
AS
 SELECT CompanyName AS C, ContactName AS CN

 FROM Customers AS CL

Once the view is created, its definition will be unreadable, as shown in Figure 9.2. Therefore, make sure that you save your view definition in a script file in case you ever need to modify it.

Figure 9.2
The definition of an encrypted view.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020