CREATE VIEW

Transact-SQL Reference

previous page next page
Transact-SQL Reference

CREATE VIEW

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.

Syntax

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 }

Arguments

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:

  • Include COMPUTE or COMPUTE BY clauses.

  • Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.

  • Include the INTO keyword.

  • Reference a temporary table or a table variable.

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.

Remarks

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.

Updatable Views

Microsoft SQL Server 2000 enhances the class of updatable views in two ways:

  • INSTEAD OF Triggers: INSTEAD OF triggers can be created on a view in order to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger allows the user to specify the set of actions that need to take place in order to process the data modification statement. Thus, if an INSTEAD OF trigger exists for a view on a given data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers, see Designing INSTEAD OF triggers.

  • Partitioned Views: If the view is of a specified form called 'partitioned view,' the view is updatable, subject to certain restrictions. Partitioned views and their updatability are discussed later in this topic.

    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:

  • The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.

  • select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

  • The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable: 
    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.

Partitioned Views

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

  1. SELECT list
    • All columns in the member tables should be selected in the column list of the view definition.

    • The columns in the same ordinal position of each select_list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

      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 >
    • The constraints should be such that any given value of <col> can satisfy at most one of the constraints C1, ..., Cn so that the constraints should form a set of disjointed or non-overlapping intervals. The column <col> on which the disjointed constraints are defined is called the 'partitioning column.' Note that the partitioning column may have different names in the underlying tables. The constraints should be in an enabled state in order for them to meet the above conditions of the partitioning column. If the constraints are disabled, re-enable constraint checking with either the WITH CHECK option or the CHECK constraint_name options of ALTER TABLE.

      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] }
    • The same column cannot be used multiple times in the SELECT list.
  2. Partitioning column
    • The partitioning column is a part of the PRIMARY KEY of the table.

    • It cannot be a computed column.

    • If there is more than one constraint on the same column in a member table, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.
  3. Member tables (or underlying tables T1, ..., Tn)
    • The tables can be either local tables or tables from other SQL Servers referenced either through a four-part name or an OPENDATASOURCE- or OPENROWSET-based name. (The OPENDATASOURCE and OPENROWSET syntax can specify a table name, but not a pass-through query.) For more information, see OPENDATASOURCE and OPENROWSET .

      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.

    • The same table cannot appear twice in the set of tables that are being combined with the UNION ALL statement.

    • The member tables cannot have indexes created on computed columns in the table.

    • The member tables should have all PRIMARY KEY constraints on an identical number of columns.

    • All member tables in the view should have the same ANSI padding setting (which is set using the user options option in sp_configure or the SET option).

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:

  • The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow NULLs. For those member table columns that have DEFAULT definitions, the statements cannot use the keyword DEFAULT explicitly.

  • The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the INSERT action will fail with a constraint violation.

  • UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table.

  • PRIMARY KEY columns cannot be modified through an UPDATE statement if the member tables have text, ntext, or image columns.

  • Columns in the view that are an IDENTITY column in one or more of the member tables cannot be modified through an INSERT or UPDATE statement.

  • If one of the member tables contains a timestamp column, the view cannot be modified through an INSERT or UPDATE statement.

  • INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

    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:

  • A distributed transaction will be started to ensure atomicity across all nodes affected by the update.

  • The XACT_ABORT SET option should be set to ON for INSERT, UPDATE, or DELETE statements to work.

  • Any smallmoney and smalldatetime columns in remote tables that are referenced in a partitioned view are mapped as money and datetime respectively. Consequently, the corresponding columns (in the same ordinal position in the select list) in the local tables should be money and datetime.

  • Any linked server in the partitioned view cannot be a loopback linked server (a linked server that points to the same SQL Server).

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:

  • If the underlying tables are involved in merge replication or transactional replication with updating subscribers, the uniqueidentifier column should also be included in the SELECT list.

  • Any INSERT actions into the partitioned view must provide a NEWID() value for the uniqueidentifier column. Any UPDATE actions against the uniqueidentifier column must supply NEWID() as the value since the DEFAULT keyword cannot be used.

  • The replication of updates made using the view is exactly the same as when replicating tables in two different databases; that is, the tables are served by different replication agents and the order of the updates is not guaranteed.
Permissions

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.

Examples
A. Use a simple CREATE VIEW

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
B. Use WITH ENCRYPTION

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)
C. Use WITH CHECK OPTION

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
D. Use built-in functions within a view

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
E. Use @@ROWCOUNT function in a view

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
F. Use partitioned data

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

See Also

ALTER TABLE

ALTER VIEW

DELETE

DROP VIEW

INSERT

Programming Stored Procedures

sp_depends

sp_help

sp_helptext

sp_rename

System Tables

UPDATE

Using Identifiers

Using Views with Partitioned Data

previous page start next page