This is the first of two parts on SQL Server views. This first tip introduces the concept of SQL Server views and shows you how to create them. Part 2 examines how to index and update base data on views.
One of the objects you can create in a SQL Server database is a view, a virtual table that retrieves data from one or more tables (or other views), similar to how a SELECT statement returns a data set. SQL Server views abstract the underlying table schema while presenting data in rows and columns like a regular table. As a result, users and applications can query a view as they would a table, but the view defines what data they can see and how that data is referenced.
You can think of SQL Server views as filters that simplify and control data access. They are essentially named queries that provide a snapshot of the data returned by that query. Not only do SQL Server views let you focus and customize how you present the data, but they also provide an extra layer of security by abstracting the underlying data structure. You can grant access to a view without granting access to the tables on which the view is based.
SQL Server views can also abstract schema changes to the underlying tables. For example, if an application retrieves data through a view and the view's structure (returned columns) remains unchanged, the view's query can be modified to accommodate changes to the underlying tables without impacting view access. A view can also be used to provide backward compatibility to emulate a table that has significantly changed or no longer exists.
In this article, I introduce you to SQL Server views and give examples that demonstrate how they work. The examples are based on two tables I created in the AdventureWorks2012 database on a local instance of SQL Server 2012. The following code shows the T-SQL I used to create the tables:
As you can see, I used two SELECT…INTO statements to define and populate the CurrentEmployee and EmployeeInfo tables. If you want to try out the exercises, you need only run the T-SQL and you'll be ready to go. If you're running a different version of the AdventureWorks database, you might have to tweak the code and examples to make them work on your system.
The CREATE VIEW syntax
To create a view in SQL Server, use the CREATE VIEW statement, which is shown in the following syntax:
Not surprisingly, you begin with the CREATE VIEW clause, providing the name and, optionally, the schema where you want to define the view. If you don't specify a schema name, the default schema is used.
Next, you can specify one or more column names, enclosed in parentheses and separated with commas. These are the columns that users see when querying the view. If you don't include column names, the view uses the names returned by the SELECT statement. If you want to use different names -- or you're creating computed columns -- you can include the column names here, after the CREATE VIEW clause, or specify column aliases within your query's SELECT list.
After the column names, you can include a WITH clause, which takes one or more of the following three arguments:
- ENCRYPTION: Encrypts the CREATE VIEW statement text and prevents SQL Server replication from publishing the view.
- SCHEMABINDING: Binds the view to the tables referenced within the SELECT statement so the tables can't be modified in any way that would impact the view.
- VIEW_METADATA: Returns the view's metadata information instead of information about the base tables. This applies when the view is queried through the DB-Library, ODBC or OLE DB API and when browse-mode metadata is being requested for the query.
After the optional WITH clause, you specify the AS keyword, followed by the query that accesses the underlying data. The query is a standard SELECT statement that retrieves data from one or more tables or views. You can also retrieve partitioned data by using the UNION operator to join SELECT statements. However, you cannot include an ORDER BY clause, unless the SELECT list includes a TOP clause. In addition, you cannot use the INTO keyword or the OPTION clause, and you cannot reference a temporary table or table variable.
The last optional element of the CREATE VIEW definition is WITH CHECK OPTION, which forces all data modification statements executed against the view to conform to the view's SELECT statement. In other words, WITH CHECK OPTION prevents data from being updated if it will impact the data returned by the view in a way that the view can no longer return that data. To better understand this option, as well as the other options related to the view definition, see the topic "CREATE VIEW (Transact-SQL)" in SQL Server Books Online.
Creating a view in SQL Server
Once you have a basic understanding of the CREATE VIEW syntax, you're ready to build your own view. The following example includes a CREATE VIEW statement that defines the vEmployees view:
First, we use an IF statement to check for the existence of the view and then drop it if it does exist. Then we run our CREATE VIEW statement. Not surprisingly, we start the statement with the CREATE VIEW clause and then specify the column names to use for the returned data. Again, you do not need to specify the column names here; you can instead use those returned by the SELECT statement.
After the column names, the statement specifies the WITH SCHEMABINDING clause to ensure the tables cannot be updated if they impact the view. Next comes the AS keyword and then the actual query.
The SELECT statement joins the CurrentEmployee and EmployeeInfo tables and returns the BusinessEntityID, FirstName and LastName columns from the CurrentEmployee table and the JobTitle and HireDate columns from the EmployeeInfo table. Notice that the view definition changes the column name BusinessEntityID to EmployeeID and changes HireDate to DateHired.
Once you've created your view, you can call it within a query as you would a table. For example, the following SELECT statement retrieves several columns of data from the vEmployees view:
Notice that we reference the column names based on how they're defined in the view. In this case, we're using EmployeeID rather than BusinessEntityID. We've also concatenated the FirstName and LastName columns and named the results FullName. We can even qualify our queries as we would with a table, as shown in the following example:
This time around, we've added a WHERE clause specifying that the year must be greater than 2002. Again, we reference the column name (DateHired) in the view, rather than the table's column name.
You can, of course, create queries that are far more complex than our two examples, but what we've shown here should give you a good idea of how a view works.
See part two on indexing and adding data to views.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.
This was first published in October 2013