Saturday, November 22, 2014

TSQL Tutorials: Views in SQL Server

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