7 Views
Where is the information?
Lost in the data.
Where is the data?
Lost in the #@%!& database!—Joe Celko
VIEWs are static queries that you can use as though they were tables. A VIEW consists of a SELECT statement compiled ahead of time using SQL’s CREATE VIEW command and referenced in the same manner as a table. VIEW columns can consist of table columns, aggregates, constants, and expressions (computed columns). Some VIEWs are updatable; some aren’t. Whether a VIEW is updatable depends largely on whether SQL Server can resolve an update to one of its rows to a single row in an underlying base table. All VIEWs must eventually reference a base table or nontabular expression (an expression that doesn’t require a table—GETDATE(), for example), though VIEWs can be “nested”—meaning that a VIEW can reference other VIEWs as long as the dependence tree eventually resolves to base tables or nontabular expressions.
Transact-SQL doesn’t support temporary VIEWs, though you can create static VIEWs in tempdb and achieve a similar effect. Also, VIEWs aren’t allowed to reference temporary tables—only references to other VIEWs or permanent base tables are permitted.
As a rule, ORDER BY is not allowed in VIEWs, so the following syntax is not valid:
-- _Not_ valid Transact-SQL syntax
CREATE VIEW myauthors AS
SELECT * FROM authors
ORDER BY au_lname
There is, however, a workaround. If you know the maximum number of rows the query might return in advance, you can use Transact-SQL’s TOP n extension to allow ORDER BY in VIEWs, like this:
Specify a number large enough to exceed the number of possible rows in the table if you’re unsure of the exact count. TOP n allows the use of ORDER BY within a VIEW by permitting you to request more top rows than actually exist in the table, resulting in all rows being returned. The query below shows that the ORDER BY is in effect when we query the view:
SELECT au_id, au_lname, au_fname
FROM myauthors
As with stored procedures, the status of SET QUOTED_IDENTIFIER and SET ANSI_ NULLS is saved with each VIEW. This means that individual session settings for these options are ignored by the VIEW when it’s queried. It also means that you can localize special quoted identifier or NULL handling to a particular VIEW without affecting anything else.
An UPDATE to a VIEW is not allowed to affect more than one underlying base table at a time. If the VIEW joins two or more tables together, an UPDATE to it may alter only one of them. Likewise, an INSERT must modify only one table at a time in a multitable VIEW. This means that values can be supplied for only one table—the columns in the other table(s) must have DEFAULT constraints, allow NULLs, or otherwise be optional. DELETE can be used only with single-table VIEWs—it can’t be used with multitable VIEWs of any kind.
Out of the box, SQL Server provides a number of VIEWs for accessing the system catalogs. These objects provide an ANSI SQL-92–compliant means of retrieving meta-data and otherwise querying the server for system-level information. You should use these rather than querying system catalog tables directly for two reasons: 1) the ANSI SQL-92 specification defines these VIEWs—so they should work similarly between different DBMS platforms, and 2) you can depend on them to work the same way between different releases of SQL Server, even though their underlying system tables may change from release to release. Table 7.1 lists the SQL-92–compliant VIEWs that SQL Server provides:
Note that you must refer to these objects using the INFORMATION_SCHEMA database schema. In SQL Server parlance, a schema and an owner are synonymous. This means that you must use:
SELECT * FROM INFORMATION_SCHEMA.TABLES
rather than:
SELECT * FROM TABLES
Unless a VIEW was created using the WITH ENCRYPTION option, you can use sp_helptext to retrieve its source code. You can also inspect and modify VIEW source code in Enterprise Manager, as well as many SQL-DMO–enabled administration tools. Here’s some sample code that returns the source of the syslogins system VIEW:
USE master
exec sp_helptext syslogins
Text
--------------------------------------------------------------------------------
CREATE VIEW syslogins AS SELECT
suid = convert(smallint, suser_id(name)),
sid = convert(varbinary(85), sid),
status = convert(smallint, 8 +
CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END),
createdate = convert(datetime, xdate1),
updatedate = convert(datetime, xdate2),
accdate = convert(datetime, xdate1),
totcpu = convert(int, 0),
totio = convert(int, 0),
spacelimit = convert(int, 0),
timelimit = convert(int, 0),
resultlimit = convert(int, 0),
name = convert(varchar(30), CASE WHEN (xstatus&4)=0 THEN name
ELSE suser_name(suser_id(name)) END),
dbname = convert(sysname, db_name(dbid)),
password = convert(sysname, password),
language = convert(sysname, language),
denylogin = convert(int, CASE WHEN (xstatus&1)=1 THEN 1 ELSE 0 END),
hasaccess = convert(int, CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END),
isntname = convert(int, CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END),
isntgroup = convert(int, CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END),
isntuser = convert(int, CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END),
sysadmin = convert(int, CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END),
securityadmin = convert(int, CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END),
serveradmin = convert(int, CASE WHEN (xstatus&64)=64 THEN 1 ELSE 0 END),
setupadmin = convert(int, CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END),
processadmin = convert(int, CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END),
diskadmin = convert(int, CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END),
dbcreator = convert(int, CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END),
loginname = convert(sysname, name)
FROM sysxlogins WHERE srvid IS NULL
As mentioned earlier, there are a number of factors affecting whether a VIEW is updatable. For a VIEW to allow updates, the following criteria must be met:
• Aggregate functions, the TOP, GROUP BY, UNION, or DISTINCT clauses or keywords are not allowed.
• Derived columns (columns constructed from complex expressions) are not updatable.
• SELECT lists consisting entirely of nontabular expressions are not allowed.
Again, the bottom line is that the server must be able to translate an update to a row in the VIEW into an update to a row in a base table. If it can’t do this, you can’t update the VIEW.
An updatable VIEW can be created so that it checks updates for compliance with its WHERE clause, if it has one. This prevents rows added via the VIEW from “vanishing” when the VIEW is requeried since they don’t meet its selection criteria. To set up a VIEW this way, use the WITH CHECK OPTION clause when you create it, like so:
CREATE VIEW CALIFORNIA_AUTHORS AS
SELECT *
FROM authors
WHERE State='CA'
WITH CHECK OPTION
This particular example ensures that any author that’s added via the VIEW resides in California. For example, this statement fails because of WITH CHECK OPTION:
INSERT CALIFORNIA_AUTHORS
VALUES ('867-53-09EI','Henderson','Ken',
'972 555-1212','57 Riverside',’Dallas',’TX','75080',1)
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.
This also applies to updates. If an update you make through a VIEW that has WITH CHECK OPTION enabled would cause the row to fail the VIEW’s WHERE criteria, the update will be rejected.
As mentioned in Chapter 6, derived tables are SELECT statements that you embed within the FROM clause of other SELECTs in place of table references. I include coverage of them here for completeness and because they resemble implicit or automatic VIEWs. Derived tables make possible certain types of queries that previously required separate VIEW objects. Here’s an example:
CREATE TABLE #1996_POP_ESTIMATE (Region char(7), State char(2), Population int)
INSERT #1996_POP_ESTIMATE VALUES ('West', 'CA',31878234)
INSERT #1996_POP_ESTIMATE VALUES (’South', ’TX',19128261)
INSERT #1996_POP_ESTIMATE VALUES ('North', 'NY',18184774)
INSERT #1996_POP_ESTIMATE VALUES (’South', 'FL',14399985)
INSERT #1996_POP_ESTIMATE VALUES ('North', 'NJ', 7987933)
INSERT #1996_POP_ESTIMATE VALUES ('East', 'NC', 7322870)
INSERT #1996_POP_ESTIMATE VALUES ('West', 'WA', 5532939)
INSERT #1996_POP_ESTIMATE VALUES ('Central','MO', 5358692)
INSERT #1996_POP_ESTIMATE VALUES ('East', 'MD', 5071604)
INSERT #1996_POP_ESTIMATE VALUES ('Central','OK', 3300902)
SELECT * FROM (SELECT TOP 5 WITH TIES State,
Region, Population=Population/1000000
FROM #1996_POP_ESTIMATE
ORDER BY Population/1000000) p
ORDER BY Population DESC
This query uses a derived table to return the five states with the lowest population among those listed in the table. It then uses an ORDER BY in the outer SELECT to sort these in descending order. Were it not for derived table support, this approach would require a separate stand-alone VIEW or a temporary table.
One subtlety worth mentioning here is the requirement for a table alias when using derived tables. Note the inclusion of the table alias in the code sample above even though it’s not used. This is a requirement of derived tables, regardless of whether your code actually uses the alias.
When you access a VIEW, a query plan is constructed by combining the original SELECT statement that was used to create the VIEW with the one you’re using to query it. The selection criteria you specified when you built the VIEW are combined with any specified by your query and the composite is passed on to the server engine for further processing.
Most VIEWs that include selection criteria impose static criteria—the selection logic that’s combined with the SELECT accessing the VIEW never changes regardless of how many times the VIEW is queried. The dynamic portion of the composite query usually comes from the user-supplied SELECT, not the VIEW. With the exception of VIEWs that use joins to link other VIEWs and tables, the criteria the VIEW supplies to filter the result set remains the same from use to use. Most of the time this is adequate, but there are times when it’s handy to be able to make use of a dynamic VIEW—a VIEW whose selection criteria varies based on factors external to it.
A dynamic VIEW is simply one whose selection criteria can change based on the evaluation of the expressions in its WHERE or HAVING clauses. This is an easy concept that can come in quite handy. Rather than evaluating to constants, these expressions return different values based on environmental or session elements. The best example of such a VIEW is one that returns a result set based on a nontabular expression. Here’s one that lists the sales for the current date, using the nontabular GETDATE() function:
CREATE VIEW DAILY_SALES AS
SELECT *
FROM sales
WHERE ord_date BETWEEN CONVERT(char(8),GETDATE(),112) AND
CONVERT(char(8),GETDATE(),112)+' 23:59:59.999'
You can add some rows to sales to see how this works:
INSERT sales
VALUES ('8042','QA879.1',GETDATE(),30,'Net 30','BU1032')
INSERT sales
VALUES ('6380',’D4482',GETDATE(),11,'Net 60','PS2091')
INSERT sales
VALUES ('6380',’D4492',GETDATE()+1,53,'Net 30','PS2091')
SELECT * FROM DAILY_SALES
stor_id ord_num ord_date qty payterms title_id
------- ---------------- ------------------------------------- ------ ----------- --------
6380 D4482 1999-06-24 19:14:33.657 30 Net 60 PS2091
8042 QA879.1 1999-06-24 19:13:26.230 30 Net 30 BU1032
This VIEW uses GETDATE() to limit the sales returned to those whose ord_date is today. The criteria actually processed by the server will vary based on the current date. Today, its WHERE clause will be expanded to today’s date, and the first two rows that were inserted will show up. Tomorrow, it will evaluate to tomorrow’s date, and the third row will show up. That’s the nature of dynamic VIEWs—the criteria that are actually processed by the server change from use to use based on external factors.
Here’s another example that uses CASE to make the VIEW even more dynamic. This code improves on the previous example by making it aware of weekends. Since no sales occur on weekends, this code returns the sales for either the previous Friday or the upcoming Monday when the current date falls on a weekend:
CREATE VIEW DAILY_SALES AS
SELECT *
FROM sales
WHERE ord_date BETWEEN
(CASE DATEPART(DW,CONVERT(char(8),GETDATE(),112))
WHEN 1 THEN CONVERT(char(8),GETDATE()+1,112)
WHEN 7 THEN CONVERT(char(8),GETDATE()-1,112)
ELSE CONVERT(char(8),GETDATE(),112)
END)
AND (CASE DATEPART(DW,CONVERT(char(8),GETDATE(),112))
WHEN 1 THEN CONVERT(char(8),GETDATE()+1,112)
WHEN 7 THEN CONVERT(char(8),GETDATE()-1,112)
ELSE CONVERT(char(8),GETDATE(),112)
END+' 23:59:59.999')
You can use other nontabular functions to create similar sliding or dynamic VIEWs. For example, SUSER_SNAME() could be used to limit the rows returned according to user name. HOST_NAME() could be used to filter based on machine name. Whatever the case, the SELECT used to query the VIEW doesn’t change (in the examples above, it’s always a simple SELECT *); only the criteria that the VIEW provides to filter the result set do.
Views are a handy mechanism for partitioning data into subsets. This partitioning can be either horizontal or vertical in nature or both. It can hide columns from inspection by unauthorized users and can group rows logically based on some predetermined criteria. Here’s an example of a vertically partitioned table:
In this example, personal information such as the employee’s home phone number and birth date is omitted from the view in order to provide a basic employee listing. Here’s an example of horizontal partitioning:
USE Northwind
GO
IF (OBJECT_ID('USA_ORDERS') IS NOT NULL)
DROP VIEW USA_ORDERS
GO
IF (OBJECT_ID('UK_ORDERS') IS NOT NULL)
DROP VIEW UK_ORDERS
GO
IF (OBJECT_ID('FRENCH_ORDERS') IS NOT NULL)
DROP VIEW FRENCH_ORDERS
GO
CREATE VIEW USA_ORDERS AS
SELECT TOP 10 EmployeeID, COUNT(*) AS NumOrdered
FROM orders
WHERE ShipCountry='USA'
GROUP BY EmployeeID
ORDER BY NumOrdered DESC
GO
CREATE VIEW UK_ORDERS AS
SELECT TOP 10 EmployeeID, COUNT(*) AS NumOrdered
FROM orders
WHERE ShipCountry='UK'
GROUP BY EmployeeID
ORDER BY NumOrdered DESC
GO
CREATE VIEW FRENCH_ORDERS AS
SELECT TOP 10 EmployeeID, COUNT(*) AS NumOrdered
FROM orders
WHERE ShipCountry='France'
GROUP BY EmployeeID
ORDER BY NumOrdered DESC
GO
PRINT 'USA ORDERS'
SELECT * FROM USA_ORDERS
GO
PRINT 'UK ORDERS'
SELECT * FROM UK_ORDERS
GO
PRINT 'FRENCH ORDERS'
SELECT * FROM FRENCH_ORDERS
GO
You learned about VIEW objects in this chapter. VIEWs offer a powerful means of presenting data in formats that differ from the way in which it’s stored in the database. They also offer an alternative to constraints and triggers for controlling data insertions and updates. SQL Server itself uses views extensively, and it’s likely that you will as well if you build sophisticated applications using Transact-SQL.