Reference Manual: Commands

Home

Reference Manual: Commands

Chapter 1 Commands

create view

Usage

You can use views as security mechanisms by granting permission on a view, but not on its underlying tables.
You can rename a view with sp_rename.
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 get an error message. If the checks are successful, create view "translates" the view into an action on the underlying table(s).
For more information about views, see the Transact-SQL User's Guide.

Restrictions on views

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 or image columns in views.
You cannot include order by or compute clauses or the keyword into in the select statements that define views.
You cannot update or insert into a view whose select statements 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." For example:
```
declare @p int
select @p = 2
create view v2
as
select * from t1 where c1 > @p
```
You cannot delete from a view whose select statements include the union operator.
create view statements can be combined 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, either make sure 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
```
You cannot use the following variable in create view statements:
```
declare @p int
select @p = 2
create view v2
as
select * from t1 where c1 > @p
```
Doing so results in error message 7351, which says, "Local or global variables not allowed in view definition."

View resolution

If you alter the structure of a view's underlying table(s) 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 the dependent view(s).

Modifying data through 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 a row through a view that includes a computed column.
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.

IDENTITY columns and views

You cannot add a new IDENTITY column to a view with the column_name = identity(precision) syntax.
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.

group by clauses and views

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 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 query returns one row for each type (6 rows):
```
select type, sum(total_sales)
from titles
group by type
```
For more information about group by, see "group by and having clauses."

distinct clauses and views

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 the distinct applies only for that view; it does not cover any new views derived from the distinct view.

with check option clauses and views

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.

Getting information about views

To get 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.

Creating views from SQL derived tables

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.
For more information about derived table expressions, see select.


	Expand Search