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.
What are the restrictions when creating view?
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
Functions can be used in the select_statement.
select_statement can use multiple SELECT statements separated by UNION or UNION ALL.
What is WITH CHECK OPTION on views?
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.
What is WITH ENCRYPTION on views?
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.
What do you know 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.
What is 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
How many columns a view can reference?
A view can reference a maximum of 1,024 columns.
Notes about views:
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.
What is 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.
What is 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.
What are then 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.
The same column cannot be used multiple times in the SELECT list.
1. 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.
2. 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).
What are the 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.
How do you alter a view?
ALTER VIEW
Alters a previously created view (created by executing CREATE VIEW), including indexed views, without affecting dependent stored procedures or triggers and without changing permissions
How do you DROP a VIEW?
Removes one or more views from the current database. DROP VIEW can be executed against indexed views.
No comments:
Post a Comment