Chapter 10

Views

WHAT YOU WILL LEARN IN THIS CHAPTER:

  • The nature of views, and how they can be used
  • How to create, alter, and drop views using T-SQL
  • View management using SSMS
  • How to use views for abstraction and security
  • An introduction to indexed (or materialized) views

Up to this point, you’ve been dealing with base objects — objects that have some level of substance of their own. In this chapter, you’re going to go virtual (well, mostly anyway), and take a look at views.

Views have a tendency to be used either too much or not enough — rarely just right. When you’re done with this chapter, you should be able to use views to:

  • Reduce apparent database complexity for end users
  • Prevent sensitive columns from being selected, while still affording access to other important data
  • Add additional indexing to your database to speed query performance — even when you’re not using the view the index is based on

A view is, at its core, nothing more than a stored query. What’s great is that you can mix and match your data from base tables (or other views) to create what will, in most respects, function just like an ordinary base table. You can create a simple query that selects from only one table and leaves some rows or columns out, or you can create a complex query that joins several tables and makes them appear as one.

CREATING SIMPLE VIEWS

The syntax for a view, in its most basic form, is a combination of a couple of things you’ve already seen in the book — the basic CREATE statement ...

Get Beginning Microsoft® SQL Server® 2012 Programming now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.