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.
Restrictions
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:
CREATE VIEW myauthors AS
SELECT TOP 50 *
FROM authors
ORDER BY au_lname
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
au_id au_lname au_fname
-----------
---------------------------------------- --------------------
409-56-7008 Bennet
Abraham
648-92-1872
Blotchet-Halls Reginald
238-95-7766 Carson
Cheryl
722-51-5454 DeFrance
Michel
712-45-1867 del
Castillo Innes
427-17-2319 Dull Ann
213-46-8915 Green
Marjorie
527-72-3246 Greene
Morningstar
472-27-2349
Gringlesby Burt
846-92-7186 Hunter
Sheryl
756-30-7391 Karsen
Livia
486-29-1786 Locksley
Charlene
724-80-9391 MacFeather
Stearns
893-72-1158 McBadden
Heather
267-41-2394 O'Leary
Michael
807-91-6654 Panteley
Sylvia
998-72-3567 Ringer
Albert
899-46-2035 Ringer
Anne
341-22-1782 Smith
Meander
274-80-9391 Straight
Dean
724-08-9931 Stringer
Dirk
172-32-1176 White
Johnson
672-71-3249 Yokomoto
Akiko
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.
DML Restrictions
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.
ANSI SQL Schema
VIEWs
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.
lists the
SQL-92–compliant VIEWs that SQL Server provides:
SQL Server's ANSI
SQL-92 schema VIEWs.
VIEW Name
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
REFERENTIAL_CONSTRAINTS
SCHEMATA
TABLE_CONSTRAINTS
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
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
Getting a VIEW's
Source Code
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
Updatable VIEWs
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.
WITH CHECK OPTION
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.
Derived Tables
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
State Region
Population
----- -------
-----------
NJ North 7
NC East 7
WA West 5
MO Central 5
MD East 5
OK Central 3
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.
Dynamic VIEWs
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
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 aweekend:
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.
Partitioning Data
Using Views
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:
USE Northwind
GO
IF
(OBJECT_ID('EMP_VIEW') IS NOT NULL)
DROP VIEW EMP_VIEW
GO
CREATE VIEW EMP_VIEW
AS
SELECT LastName,
FirstName,
149
Title,
Extension
FROM employees
GO
SELECT * FROM
EMP_VIEW
LastName FirstName
Title Extension
--------------------
---------- ---------------------------- ---------
Davolio Nancy Sales
Representative 5467
Fuller Andrew Vice
President, Sales 3457
Leverling Janet
Sales Representative 3355
Peacock Margaret
Sales Representative 5176
Buchanan Steven
Sales Manager 3453
Suyama Michael Sales
Representative 428
King Robert Sales
Representative 465
Callahan Laura
Inside Sales Coordinator 2344
Dodsworth Anne Sales
Representative 452
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
Guru’s Guide to
Transact-SQL
150
GO
PRINT 'FRENCH
ORDERS'
SELECT * FROM
FRENCH_ORDERS
GO
USA ORDERS
EmployeeID
NumOrdered
-----------
-----------
4 22
1 21
3 21
8 19
6 14
2 9
7 7
5 6
9 3
UK ORDERS
EmployeeID
NumOrdered
-----------
-----------
4 12
1 9
3 8
8 6
2 5
6 5
7 5
9 4
5 2
FRENCH ORDERS
EmployeeID
NumOrdered
-----------
-----------
4 14
3 13
2 11
1 9
6 9
8 8
5 5
7 5
9 3
No comments:
Post a Comment