Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.
CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
< view_attribute > ::=
{ ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
view_name
Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.
column
Is the name to be used for a column in a view. Naming a column in CREATE VIEW is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns may otherwise have the same name (usually because of a join), or when a column in a view is given a name different from that of the column from which derived. Column names can also be assigned in the SELECT statement.
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.
n
Is a placeholder that indicates that multiple columns can be specified.
AS
Are the actions the view is to perform.
select_statement
Is the SELECT statement that defines the view. It can use more than one table and other views. To select from the objects referenced in the SELECT clause of a view created, it is necessary to have the appropriate permissions.
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.
In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.
There are 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 or UNION ALL.
WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.
WITH ENCRYPTION
Indicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
SCHEMABINDING
Binds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
VIEW_METADATA
Specifies that SQL Server will return to the DBLIB, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata returned by SQL Server to the client-side DB-LIB, ODBC, and OLE DB APIs, which allow the client-side APIs to implement updatable client-side cursors. Browse-mode meta data includes information about the base table that the columns in the result set belong to.
For views created with VIEW_METADATA option, the browse-mode meta data returns the view name as opposed to the base table names when describing columns from the view in the result set.
When a view is created WITH VIEW_METADATA, all its columns (except for timestamp) are updatable if the view has INSERT or UPDATE INSTEAD OF triggers. See Updatable Views later in this topic.
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 was dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, and 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 recreated.
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; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.
The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.
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 when accessing the view.
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 or higher than 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
Microsoft SQL Server 2000 enhances the class of updatable views in two ways:
When needed, SQL Server will distinguish Local Partitioned Views as the views in which all participating tables and the view are on the same SQL Server, and Distributed Partitioned Views as the views in which at least one of the tables in the view resides on a different (remote) server.
If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:
CREATE VIEW NoTable AS
SELECT GETDATE() AS CurrentDate,
@@LANGUAGE AS CurrentLanguage,
CURRENT_USER AS CurrentUser
INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable, as specified in the conditions above. 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 the FROM clause of the view. A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause.
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same SQL Server or in a group of autonomous SQL Server 2000 servers, called Federated SQL Server 2000 Servers.
For example, if you have Customers table data distributed in three member tables in three server locations (Customers_33 on Server1, Customers_66 on Server2, and Customers_99 on Server3), a partitioned view on Server1 would be defined this way:
--Partitioned view as defined on Server1
CREATE VIEW Customers
AS
--Select from local member table
SELECT *
FROM CompanyData.dbo.Customers_33
UNION ALL
--Select from member table on Server2
SELECT *
FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
--Select from mmeber table on Server3
SELECT *
FROM Server3.CompanyData.dbo.Customers_99
In general, a view is said to be a partitioned view if it is of the following form:
SELECT <select_list1>
FROM T1
UNION ALL
SELECT <select_list2>
FROM T2
UNION ALL
...
SELECT <select_listn>
FROM Tn
Conditions for Creating Partitioned Views
Also, at least one column (for example <col>) must appear in all the SELECT lists in the same ordinal position. This <col> should be defined such that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col> respectively.
Constraint C1 defined on table T1 must follow this form:
C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
< simple_interval > :: =
< col > { < | > | <= | >= | = }
| < col > BETWEEN < value1 > AND < value2 >
| < col > IN ( value_list )
| < col > { > | >= } < value1 > AND
< col > { < | <= } < value2 >
Here are some examples of valid sets of constraints:
{ [col < 10], [col between 11 and 20] , [col > 20] }
{ [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }
If one or more of the member tables are remote, the view is called distributed partitioned view, and additional conditions apply. They are discussed later in this section.
Conditions for Modifying Partitioned Views
Only the Developer and Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE operations on partitioned views. To modify partitioned views, the statements must meet these conditions:
Note To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.
Additional Conditions for Distributed Partitioned Views
For distributed partitioned views (when one or more member tables are remote), the following additional conditions apply:
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE actions that involve updatable partitioned views and remote tables.
When the member tables and partitioned view definition are in place, Microsoft SQL Server 2000 builds intelligent plans that use queries efficiently to access data from member tables. With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. When a user issues a query, the query processor compares the map to the values specified in the WHERE clause, and builds an execution plan with a minimal amount of data transfer between member servers. Thus, although some member tables may be located in remote servers, SQL Server 2000 will resolve distributed queries so that the amount of distributed data that has to be transferred is minimal. For more information about how SQL Server 2000 resolves queries on partitioned views, see Resolving Distributed Partitioned Views.
Considerations for Replication
In order to create partitioned views on member tables that are involved in replication, the following considerations apply:
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.
To create a view, the user must have CREATE VIEW permission along with SELECT permission on the tables, views, and table-valued functions being referenced in the view, and EXECUTE permission on the scalar-valued functions being invoked in the view.
In addition, to create a view WITH SCHEMABINDING, the user must have REFERENCES permissions on each table, view, and user-defined function that is referenced.
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 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
----------- ------------------------------------------------------------
661577395
???????????????????????????????????????????????????????????????????????...
(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 the tables and insert the values CREATE TABLE SUPPLY1 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150), supplier CHAR(50) ) CREATE TABLE SUPPLY2 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300), supplier CHAR(50) ) CREATE TABLE SUPPLY3 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450), supplier CHAR(50) ) CREATE TABLE SUPPLY4 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600), supplier CHAR(50) ) INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp') INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd') INSERT SUPPLY2 VALUES ('231', 'FarEast') INSERT SUPPLY2 VALUES ('280', 'NZ') INSERT SUPPLY3 VALUES ('321', 'EuroGroup') INSERT SUPPLY3 VALUES ('442', 'UKArchip') INSERT SUPPLY4 VALUES ('475', 'India') INSERT SUPPLY4 VALUES ('521', 'Afrique') --create the view that combines all supplier tables CREATE VIEW all_supplier_view AS SELECT * FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4