Views and Derived Tables

Think of a view as a virtual table. Simply put, a view is a named SELECT statement that dynamically produces a result set that you can further operate on. A view doesn't actually store any data. It acts as a filter for underlying tables in which the data is stored. The SELECT statement that defines the view can be from one or more underlying tables or from other views. To relieve users of the complexity of having to know how to write an outer join properly, we can turn the previous outer-join query into a view:

 CREATE VIEW outer_view AS ( SELECT 'Author'=RTRIM(au_lname) + ', ' + au_fname, 'Title'=title FROM (titleauthor AS TA FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)) RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id) WHERE A.state <> 'CA' OR A.state IS NULL ) 

Now, instead of formulating the outer join, we can simply query the outer-join view, outer_view. Then we can do a search for author names starting with Ri.

 SELECT * FROM outer_view WHERE Author LIKE 'Ri%' ORDER BY Author 

Here's the output:

 Author Title -------------- --------------------- Ringer, Albert Is Anger the Enemy? Ringer, Albert Life Without Fear Ringer, Anne The Gourmet Microwave Ringer, Anne Is Anger the Enemy? 

Notice that the view defines the set of rows that are included, but it doesn't define their order. If we want a specific order, we must specify the ORDER BY clause in the SELECT statement on the view rather than on the SELECT statement that defines the view.

A derived table is a fancy name for the result of using another SELECT statement in the FROM clause of a SELECT statement. This works because the result of a SELECT statement is a table, which is exactly what the FROM clause requires. You can think of a view as a named derived table. A view is named, and its definition is persistent and reusable; a derived table is a completely dynamic, temporal concept. To show the difference, here's an equivalent LIKE 'Ri%' query using a derived table instead of a view:

 SELECT * FROM (SELECT 'Author'=RTRIM(au_lname) + ',' + au_fname, 'Title'=title FROM (titleauthor AS TA FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)) RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id) WHERE A.state <> 'CA' OR A.state IS NULL ) AS T WHERE T.Author LIKE 'Ri%' 

You can insert, update, and delete rows in a view but not in a derived table. Keep in mind that you're always modifying rows in the tables on which a view is based, because the view has no data of its own. The definition of the view is persistent, but the data is not. The data exists only in the view's base tables. Think of it as modifying data through a view rather than modifying data in a view. Some limitations to modifying data through a view exist; the SQL Server documentation explains them, but they bear repeating here with a bit more comment.

The ability to modify data through a view is determined by the characteristics of the view, the properties of the base tables, and the actual change being made. If the view has INSTEAD-OF triggers, then none of the following rules for modification apply because the INSTEAD-OF trigger replaces the modification operations with other actions. (I'll discuss INSTEAD-OF triggers in Chapter 12.) If the view is a partitioned view, it has its own rules for modification (as discussed in the next section). If the view does not have relevant INSTEAD-OF triggers and is not a partitioned view, it is considered updateable if it does not contain TOP, GROUP BY, UNION, or DISTINCT. You can use aggregate functions only in a subquery in the view. So if the view is considered updateable by these rules, the following restrictions apply to the actual modification statements:

Modifications restricted to one base table Data modification statements (INSERT and UPDATE only) are allowed on multiple-table views if the data modification statement affects only one base table. DELETE statements are never allowed on multiple-table views.

INSERT statements and NOT NULL columns INSERT statements aren't accepted unless all the NOT NULL columns without defaults in the underlying table or view are included in the view through which you're inserting new rows and values for those columns are included in the INSERT statement. SQL Server has no way to supply values for NOT NULL columns in the underlying table or view if no default value has been defined.

Data restrictions All columns being modified must adhere to all restrictions for the data modification statement as if they were executed directly against the base table. This applies to column nullability, constraints, identity columns, and columns with rules and/or defaults and base table triggers.

Limitations on INSERT and UPDATE statements INSERT and UPDATE statements can't add or change any column in a view that's a computation or the result of an aggregate function.

READTEXT or WRITETEXT You can't use READTEXT or WRITETEXT on text or image columns in views.

Modifications and view criteria By default, data modification statements through views aren't checked to determine whether the rows affected are within the scope of the view. For example, you can issue an INSERT statement for a view that adds a row to the underlying base table, but that doesn't add the row to the view. This behavior occurs because the column values are all valid to the table, so they can be added; however, if the column values don't meet the view's criteria, they aren't represented in the selection for the view. Similarly, you can issue an UPDATE statement that changes a row in such a way that the row no longer meets the criteria for the view. If you want all modifications to be checked, use the WITH CHECK OPTION option when you create the view.

For the most part, these restrictions are logical and understandable, except for WITH CHECK OPTION. For instance, if you use a view that defines some select criteria and allows the rows produced by the view to be modified, you must use WITH CHECK OPTION or you might experience the bizarre results of inserting a row that you can never see or updating rows in such a way that they disappear from the view.

Here's an example:

 CREATE VIEW CA_authors AS ( SELECT * FROM authors WHERE state='CA' ) GO SELECT * FROM CA_authors -- (returns 15 rows) BEGIN TRAN UPDATE CA_authors SET state='IL' SELECT * FROM CA_authors -- (returns 0 rows) ROLLBACK TRAN 

Should you be able to update rows in a way that causes them to disappear from the view or add rows and never see them again? It's a matter of opinion. But because the SQL Server behavior is as ANSI SQL specifies, and such disappearing rows are as specified, you should consider always using WITH CHECK OPTION if a view is to be updated and the criteria are such that WITH CHECK OPTION might be violated.

If we rewrite the view as follows

 CREATE VIEW CA_authors AS ( SELECT * FROM authors WHERE state='CA' ) WITH CHECK OPTION 

when we attempt to update the state column to IL, the command fails and we get this error message:

 Server: Msg 550, Level 16, State 1, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated. 

Views also have an often-overlooked ability to use a system function in the view definition, making it dynamic for the connection. Suppose we keep a personnel table that has a column called sqldb_name, which is the person's login name to SQL Server. The function SUSER_SNAME (server_user_id) returns the login name for the current connection. We can create a view using the SUSER_SNAME system function to limit that user to only his or her specific row in the table:

 CREATE VIEW My_personnel AS ( SELECT * FROM personnel WHERE sqldb_name = SUSER_SNAME() ) WITH CHECK OPTION 

SQL Server provides system functions that return the application name, database username (which might be different from the server login name), and workstation name, and you can use these functions in similar ways. You can see from the previous example that a view can also be important in dealing with security. For details on the system functions, see the SQL Server documentation.

Altering Views

SQL Server 2000 allows you to alter the definition of a view. The syntax is almost identical to the syntax for creating the view initially:

 ALTER VIEW view_name  [(column [,…n])] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] 

The big difference is that view_name must already exist and the definition specified replaces whatever definition the view had before ALTER VIEW was executed. You might wonder why this is a useful command, since the entire definition must be reentered in the ALTER VIEW. Why don't we just drop the view and re-create it? The benefit comes from the fact that the view's object_id won't change, which means that all the internal references to this view remain intact. If other views reference this one, they'll be unaffected. If you've assigned permissions on this view to various users and roles, dropping the view means that all permission information is lost. Altering the view keeps the permissions intact.

There is one gotcha when you deal with permissions on views if the permissions have been assigned on individual columns. Internally, SQL Server keeps track of permissions by the column ID number. If you alter a view and change the order of the columns, the permissions might no longer be what you expect. Suppose you've created a view on the authors table to select the authors' names and phone numbers. You want all users to be able to see the authors' names, but you want the phone numbers to be unavailable.

 CREATE VIEW authorlist (first, last, phone) AS SELECT au_fname, au_lname, phone FROM authors GO GRANT SELECT ON authorlist (first, last) TO PUBLIC GO 

All users in the pubs database can now execute this command:

 SELECT first, last FROM authorlist 

You can then alter the view to put the phone number column in the first position:

 ALTER VIEW authorlist (phone, first, last) AS SELECT phone, au_fname, au_lname FROM authors 

Because the permissions have been granted on the first two columns, users will get an error message when they run this query:

 Server: Msg 230, Level 14, State 1 SELECT permission denied on column 'last' of object 'authorlist', database 'pubs', owner 'dbo'. 

However, all users can now select the values in the phone column, which you're trying to keep hidden.

Partitioned Views

Views in SQL Server can contain the UNION ALL clause, and if these UNION views meet certain conditions, they are called partitioned views. Partitioned views let you separate disjoint sets of similar data into separate tables, but through the view, all the tables can be accessed simultaneously. For example, if you were to separate your sales data by quarter so that each quarter was in its own table, you could create a view for the year's sales that unioned the data from all the quarterly tables. Alternatively, you could have sales data for each region of the country in a separate table and use UNION ALL to combine all the regional tables into a view for the whole country.

A view can be considered a partitioned view if it meets all the following criteria:

• The columns in the corresponding positions in each SELECT list are of the same exact type, including having the same collation.
• One column in each table, in the same position, must have a CHECK constraint defined on it that is mutually exclusive with the corresponding constraints in the other tables. This column is called the partitioning column. For example, one table could have the constraint (region = 'East'), one table could have the constraint (region = 'North'), one could have (region = 'West'), and another could have (region = 'South'). No row could satisfy more than one of these constraints.
• The partitioning column cannot be a computed column.
• If any of the underlying tables exists on servers other than the local SQL Server on which the view is defined, the view is called a distributed partitioned view and is subject to additional restrictions. You can find out more about distributed partitioned views in SQL Server Books Online.
• The same underlying table cannot appear more than once in the view.
• The underlying tables cannot have indexes on computed columns.

  Here's an example of a partitioned view:

   -- Create the tables and insert the values CREATE TABLE Sales_West ( Ordernum INT, total money, region char(5) check (region = 'West'), primary key (Ordernum, region) ) CREATE TABLE Sales_North ( Ordernum INT, total money, region char(5) check (region = 'North'), primary key (Ordernum, region) ) CREATE TABLE Sales_East ( Ordernum INT, total money, region char(5) check (region = 'East'), primary key (Ordernum, region) ) CREATE TABLE Sales_South ( Ordernum INT, total money, region char(5) check (region = 'South'), primary key (Ordernum, region) ) GO INSERT Sales_West VALUES (16544, 2465, 'West') INSERT Sales_West VALUES (32123, 4309, 'West') INSERT Sales_North VALUES (16544, 3229, 'North') INSERT Sales_North VALUES (26544, 4000, 'North') INSERT Sales_East VALUES ( 22222, 43332, 'East') INSERT Sales_East VALUES ( 77777, 10301, 'East') INSERT Sales_South VALUES (23456, 4320, 'South') INSERT Sales_South VALUES (16544, 9999, 'South') GO -- Create the view that combines all sales tables CREATE VIEW Sales_National AS SELECT * FROM Sales_West UNION ALL SELECT * FROM Sales_North UNION ALL SELECT * FROM Sales_East UNION ALL SELECT * FROM Sales_South 

• Partitioned views are updateable under certain conditions. In addition to meeting all the above criteria for the view to be partitioned, the view must meet these restrictions: the primary key column of each table should be included in the select list and must include the partitioning column.
• All columns in the underlying tables not included in the SELECT list should allow NULLs. Default constraints cannot be defined on these columns.

To INSERT into a partitioned view, you must supply a value for all non-nullable columns in the view, even if the column has a default defined in the underlying table. In addition, the value being inserted into the partitioning column must satisfy one of the CHECK constraints in one of the underlying tables; otherwise, you'll get an error message stating that a constraint violation has occurred.

The SQL Server optimizer can be quite clever about processing queries that access partitioned views. In particular, it can determine that it does not need to look at every table in the view, but only the table that contains the rows that match the value of the partitioning column. Here's an example:

 SELECT * FROM Sales_National WHERE region = 'South' 

For this query, the optimizer can determine that the only table that needs to be accessed is the Sales_South table. You can see that this is the case by inspecting the query plan produced when you execute this query. We'll look at the details of examining query plans in Chapter 16.