Creates a view, which is an alternative way to look at the data in one or more tables.
create view [owner.]view_name [(column_name[, column_name]...)] as select [distinct] select_statement [with check option]
is the name of the view. The name cannot include the database name. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the view name cannot be a variable and must conform to the rules for identifiers. Specify the owner’s name to create another view of the same name owned by a different user in the current database. The default value for owner is the current user.
specifies names to be used as headings for the columns in the view. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the column name must conform to the rules for identifiers.
You can always supply column names, but they are required only on:
A column is derived from an arithmetic expression, function, string concatenation, or constant
Two or more columns have the same name (usually because of a join)
You want to give a column in a view a different name than the column from which it is derived (see Example 3)
Column names can also be assigned in the select statement (see Example 4). If no column names are specified, the view columns acquire the same names as the columns in the select statement.
begins the select statement that defines the view.
specifies that the view cannot contain duplicate rows.
completes the select statement that defines the view. The select statement can use more than one table, and other views.
indicates that all data modification statements are validated against the view selection criteria. All rows inserted or updated through the view must remain visible through the view.
Creates a view derived from the title, type, price, and pubdate columns of the base table titles:
create view titles_view as select title, type, price, pubdate from titles
Creates “new view” from “old view.” Both columns are renamed in the new view. All view and column names that include embedded blanks are enclosed in double quotation marks. Before creating the view, you must use set quoted_identifier on.
create view "new view" ("column 1", "column 2")
as select col1, col2 from "old view"
Creates a view that contains the titles, advances, and amounts due for books that have a price less than $5.00:
create view accounts (title, advance, amt_due) as select title, advance, price * total_sales from titles where price > $5
Creates a view derived from two base tables, authors and publishers. The view contains the names and cities of authors who live in a city in which there is a publisher:
create view cities (authorname, acity, publishername, pcity) as select au_lname, authors.city, pub_name, publishers.city from authors, publishers where authors.city = publishers.city
Creates a view with the same definition as in the previous example, but with column headings included in the select statement:
create view cities2 as select authorname = au_lname, acity = authors.city, publishername = pub_name, pcity = publishers.city from authors, publishers where authors.city = publishers.city
Creates a view, author_codes, derived from titleauthor that lists the unique author identification codes:
create view author_codes as select distinct au_id from titleauthor
Creates a view, price_list, derived from title that lists the unique book prices:
create view price_list (price) as select distinct price from titles
Creates a view of the stores table that excludes information about stores outside of California. The with check option clause validates each inserted or updated row against the view’s selection criteria. Rows for which state has a value other than “CA” are rejected:
create view stores_cal as select * from stores where state = "CA" with check option
Creates a view, stores_cal30, which is derived from stores_cal. The new view inherits the check option from stores_cal. All rows inserted or updated through stores_cal30 must have a state value of “CA”. Because stores_cal30 has no with check option clause, you can insert or update rows through stores_cal30 for which payterms has a value other than “Net 30”:
create view stores_cal30 as select * from stores_cal where payterms = "Net 30"
Creates a view, stores_cal30_check, derived from stores_cal. The new view inherits the check option from stores_cal. It also has a with check option clause of its own. Each row that is inserted or updated through stores_cal30_check is validated against the selection criteria of both stores_cal and stores_cal30_check. Rows with a state value other than “CA” or a payterms value other than “Net 30” are rejected:
create view stores_cal30_check as select * from stores_cal where payterms = "Net 30" with check option
Uses a SQL-derived table in creating a view:
create view psych_titles as select * from (select * from titles where type = "psychology") dt_psych
You can use views as security mechanisms by granting permission on a view, but not on its underlying tables.
You can use sp_rename to rename a view.
When you query through a view, Adaptive 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 update commands do not violate data integrity rules. If any of these checks fail, you see an error message. If the checks are successful, create view “translates” the view into an action on the underlying tables.
For more information about views, see the Transact-SQL Users Guide.
You can create a view only in the current database.
The number of columns referenced by a view cannot exceed 1024.
You cannot create a view on a temporary table.
You cannot create a trigger or build an index on a view.
You cannot use readtext or writetext on text, unitext, or image columns in views.
You cannot include order by, compute clauses, or the keyword into in the select statements that define views.
You cannot update, insert, or delete from a view with select statements that include the union operator.
If you create a view using a local or a global variable, Adaptive Server issues error message 7351: “Local or global variables not allowed in view definition.”
You can combine create view statements with other SQL statements in a single batch.
WARNING! When a create view command occurs within an if...else block or a while loop, Adaptive Server creates the schema for the view before determining whether the condition is true. This may lead to errors if the view already exists. To avoid this, verify that a view with the same name does not already exist in the database or use an execute statement, as follows:
if not exists
(select * from sysobjects where name="mytable")
begin
execute ("create table mytable (x int)")
end
If you alter the structure of a view’s underlying tables by adding or deleting columns, the new columns do not appear in a view defined with a select * clause unless the view is dropped and redefined. The asterisk shorthand is interpreted and expanded when the view is first created.
If a view depends on a table or view that has been dropped, Adaptive Server produces an error message when anyone tries to use the view. If a new table or view with the same name and schema is created to replace the one that has been dropped, the view again becomes usable.
You can redefine a view without redefining other views that depend on it, unless the redefinition makes it impossible for Adaptive Server to translate any dependent views.
delete statements are not allowed on multitable views.
insert statements are not allowed unless all not null columns in the underlying table or view are included in the view through which you are inserting new rows. Adaptive Server cannot supply values for not null columns in the underlying table or view.
You cannot insert directly to a computed column through a view. The value of computed columns can only be generated internally by Adaptive Server.
insert statements are not allowed on join views created with distinct or with check option.
update statements are allowed on join views with check option. The update fails if any of the affected columns appear in the where clause, in an expression that includes columns from more than one table.
If you insert or update a row through a join view, all affected columns must belong to the same base table.
You cannot update or insert into a view defined with the distinct clause.
Data update statements cannot change any column in a view that is a computation, and cannot change a view that includes aggregates.
You cannot use the column_name = identity (precision) syntax to add a new IDENTITY column to a view.
To insert an explicit value into an IDENTITY column, the table owner, database owner, or system administrator must set identity_insert table_name on for the column’s base table, not through the view through which it is being inserted.
When creating a view for security reasons, be careful when using aggregate functions and the group by clause. A Transact-SQL extension allows you to name columns that do not appear in the group by clause. If you name a column that is not in the group by clause, Adaptive Server returns detailed data rows for the column. For example, this Transact- SQL extended column query returns a row for every 18 rows—more data than you might intend:
select title_id, type, sum (total_sales) from titles group by type
While this ANSI-compliant query returns one row for each type (6 rows):
select type, sum (total_sales) from titles group by type
See “group by and having clauses.”
The distinct clause defines a view as a database object that contains no duplicate rows. A row is defined to be a duplicate of another row if all of its column values match the same column values in another row. Null values are considered to be duplicates of other null values.
Querying a subset of a view’s columns can result in what appear to be duplicate rows. If you select a subset of columns, some of which contain the same values, the results appear to contain duplicate rows. However, the underlying rows in the view are still unique. Adaptive Server applies the distinct requirement to the view’s definition when it accesses the view for the first time (before it does any projection and selection) so that all the view’s rows are distinct from each other.
You can specify distinct more than once in the view definition’s select statement to eliminate duplicate rows, as part of an aggregate function or a group by clause. For example:
select distinct count (distinct title_id), price from titles
The scope of distinct applies only for that view; it does not cover any new views derived from the distinct view.
If a view is created with check option, each row that is inserted or updated through the view must meet the selection criteria of the view.
If a view is created with check option, all views derived from the “base” view must satisfy its check option. Each row inserted or updated through the derived view must remain visible through the base view.
To create a report of the tables or views on which a view depends, and of objects that depend on a view, execute sp_depends.
To display the text of a view, which is stored in syscomments, execute sp_helptext with the view name as the parameter.
To create a view using a SQL-derived table, add the derived table expression in the from clause of the select part of the create view statement (see Example 11).
A view created using a SQL-derived table can be updated if the derived table expression can be updated. The update rules for the derived table expression follow the update rules for the select part of the create view statement.
Data can be inserted through a view that contains a SQL-derived table if the insert rules and permission settings for the derived table expression follow the insert rules and permission settings for the select part of the create view statement.
Temporary tables and local variables are not permitted in a derived table expression that is part of a create view statement.
SQL-derived tables cannot have unnamed columns.
For more information about derived table expressions, see the Transact-SQL Users Guide.
ANSI SQL – Compliance level: Entry-level compliant.
The use of more than one distinct keyword and the use of “column_heading = column_name” in the select list are Transact-SQL extensions.
create view permission defaults to the database owner, who can transfer it to other users.
Permissions on objects at view creation When you create a view, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the view. Therefore, you can create successfully a view even if you do not have access to its objects. All permission checks occur when a user invokes the view.
Permissions on objects at view execution When a view is invoked, permission checks on its objects depend on whether the view and all referenced objects are owned by the same user.
If the view and its objects are not owned by the same user, the invoker must have been granted direct access to the objects. For example, if the view performs a select from a table the invoker cannot access, the select statement fails.
If the view and its objects are owned by the same user, special rules apply. The invoker automatically has implicit permission to access the view’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables, you can give them restricted access with a view. In this way, a view can be a security mechanism. For example, invokers of the view might be able to access only certain rows and columns of your table. A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.
If a column in the table is encrypted, you must have decrypt permission to select from the view. If the view and its objects are not owned by the same user, you must have decrypt permission on the encrypted column in the table to select from the view. If the view and its objects are owned by the same user, it is sufficient to grant decrypt permission to the user who must select from the view on the view column that corresponds to the encrypted column in the table.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
16 |
create |
create view |
|
Documentation “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks
Commands create schema, drop view, select, update
System procedures sp_depends, sp_help, sp_helptext, sp_rename
