This is the second of two parts on SQL Server views. This tip examines how to index and update base data. The first introduced the concept of SQL Server views and showed how to create
You can create indexes on SQL Server views as long as you meet a number of conditions. For example, you must ensure that your SET options are configured correctly on all tables that participate in the view. In addition, the view's query must be deterministic; that is, the query must return the same results when the input values are repeated. You cannot, for example, use the getdate() function in the query because the value returned is always different. In addition, you must always include the WITH SCHEMABINDING clause in your view definition.
These are only some of the considerations to take into account if you want to create an index on a view. For more details about the requirements, refer to the TechNet article "Create Indexed Views."
The first index you create on a view must be a unique clustered index. After that index has been added, you can create nonclustered indexes. Keep in mind, however, that you might want to avoid indexing a view if the underlying data is updated frequently because the index itself will have to be constantly updated, which can impact the performance of other operations. But if the source data is relatively stable, indexed views can significantly improve query performance. For example, suppose we generated an execution plan when we ran the SELECT statement as in the example in part 1. Figure 1 shows what that execution plan might look like.
Figure 1: A nonindexed view accesses the base tables when querying the view.
Not surprisingly, this is a fairly basic query. Neither source table is indexed so the database engine performs table scans to retrieve the data. But even if the tables were indexed, the database engine must query two tables and then join the data together. Now let's create an index on the table. The following statement defines a unique clustered index on the view's EmployeeID column:
As you can see, creating an index on a view is like creating an index on a table. If, after creating the index, we rerun the same query, we'll see a very different execution plan, as shown in Figure 2.
Figure 2: An indexed view does not access the base tables when the view is queried.
Even in this simple scenario, the execution is cleaner. Only the clustered index needs to be accessed in order to return the data. Of course, measuring performance is a much more complex issue than what's demonstrated here. Deciding to index a view depends on how you query and modify data and the complexity of those queries, but in certain situations, an indexed view can represent a significant boost in performance.
Using a view to update base data
In some cases, you can use a view to modify data in the tables referenced by the view. However, as with indexed views, there are many rules governing your ability to update data. For example, you can modify data in only one table at a time, regardless of how many tables are referenced by the view. In addition, you can modify data through a view only if the database engine can unambiguously track the column in the view definition to the underlying base table. For example, you can't update data that has been grouped and aggregated. (For more details about the rules that govern your ability to update data through a view, refer once again to the topic “CREATE VIEW (Transact-SQL)” in SQL Server Books Online.)
Assuming you meet all the criteria for updating data through a view, the process itself is fairly straightforward and is similar to modifying data directly in a table. For example, the following UPDATE statement updates an employee's job title:
As you can see, we reference the view in the UPDATE clause as we would a table. Then we use the SET clause to provide the updated value and the WHERE clause to specify which row to update. Notice that we can use the FirstName and LastName values from the CurrentEmployee table to update the JobTitle column in the EmployeeInfo table. Because the two tables are joined within the view, we do not have to join those tables when we update the data.
We can also use the view to insert data. For example, the following INSERT INTO statement uses the vEmployees view to add a new employee:
As you'll recall from the view definition, the view's EmployeeID column directly maps to the BusinessEntityID column in the CurrentEmployee table, the same table that contains the FirstName and LastName columns. We can perform this update because all three columns are in the same table. Had we also tried to add a job title and hire date, our statement would have failed.
Working with SQL Server views
Views provide a valuable tool for exposing SQL Server data to users and their applications. They simplify the underlying data structure and provide an extra layer of security. They can be indexed to improve performance and can even be used to modify data. Plus, view definitions port easily from one version of SQL Server to the next. In fact, the basic CREATE VIEW syntax has not changed since SQL Server 2000. Clearly, you have much to gain by implementing views in your SQL Server database. They're simple to create and easy to use. If you can write a SELECT statement, you can define a view.
See part one on defining and creating 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