Creates a virtual table that represents the data in one or more tables in an alternative way. Views can be used as security mechanisms by granting permission on a view but not on the underlying (base) tables.
CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]
If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.
Note In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the title_id column in a CREATE VIEW statement, an ALTER VIEW statement can name the title_id column with a different column name, such as qty, and still have the permissions associated with the view using title_id.
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity.
There are, however, a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:
Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause. For more information, see FROM and SELECT.
Functions can be used in the select_statement.
select_statement can use multiple SELECT statements separated by UNION to create a query using partitioned data.
For partitioned data to be used in ALTER VIEW or CREATE VIEW, constraint values must be able to be verified. If constraint checking has been disabled, reenable constraint checking with either the WITH CHECK option or the CHECK constraint_name options of ALTER TABLE. It is not necessary to have constraints to use partitioned data; however, query optimization generates less optimal plans without constraints. For more information, see Scenarios for Using Views.
A view can be created only in the current database. A view can reference a maximum of 1,024 columns.
When querying through a view, Microsoft® SQL Server™ checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table(s).
If a view depends on a table (or view) that has been dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, even if the table structure does not change from the previous base table, to replace the one dropped, the view again becomes usable. If the new table (or view) structure changes, then the view must be dropped and re-created.
When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; but when a view is executed for the first time, only its query tree is stored in procedure cache. Each time a view is accessed, its execution plan is recompiled.
A view is updatable if:
CREATE VIEW NoTable AS
SELECT GETDATE() AS CurrentDate,
@@LANGUAGE AS CurrentLanguage,
CURRENT_USER AS CurrentUser
INSERT, UPDATE, and DELETE statements must also meet certain qualifications before they can reference an updatable view. UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in FROM clause of the view. A DELETE statement can reference only a view that is updatable; the view must also reference exactly one table in its FROM clause.
SQL Server saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are restored when the view is used. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS is ignored.
Note Whether SQL Server interprets an empty string as a single space or as a true empty string is controlled by the setting of sp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
CREATE VIEW permission defaults to the members of the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE VIEW permission to other users.
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO
This example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'accounts')
DROP VIEW accounts
GO
CREATE VIEW accounts (title, advance, amt_due)
WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $5
GO
Here is the result set:
The object's comments have been encrypted.
Here is the query to retrieve the identification number and text of the encrypted stored procedure:
USE pubs
GO
SELECT c.id, c.text
FROM syscomments c, sysobjects o
WHERE c.id = o.id and o.name = 'accounts'
GO
Here is the result set:
Note The text column output is shown on a separate line. When the procedure is executed, this information appears on the same line as the id column information.
id text
----------- ------------------------------------------------------------
1925581898 ??????????????????????????????????????????????_??????????????????????????????????????????????????????????????????????????????????????????????????????????
(1 row(s) affected)
This example shows a view named CAonly that allows data modifications to apply only to authors within the state of California.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'CAonly')
DROP VIEW CAonly
GO
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTION
GO
This example shows a view definition that includes a built-in function. When you use functions, the derived column must include a column name in the CREATE VIEW statement.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'categories')
DROP VIEW categories
GO
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO
This example uses the @@ROWCOUNT function as part of the view definition.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
SELECT au_lname, au_fname, @@ROWCOUNT AS bar
FROM authors
WHERE state = 'UT'
GO
SELECT *
FROM myview
This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries.
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION
SELECT *
FROM SUPPLY2
UNION
SELECT *
FROM SUPPLY3
UNION
SELECT *
FROM SUPPLY4
| ALTER TABLE | sp_depends |
| ALTER VIEW | sp_help |
| DELETE | sp_helptext |
| DROP VIEW | sp_rename |
| Using Identifiers | System Tables |
| INSERT | UPDATE |
| Programming Stored Procedures | Using Views with Partitioned Data |