View in SQL Server (bottom) reprint

In the original: View in SQL Server (bottom) http://www.cnblogs.com/xbf321/archive/2009/06/19/view_two_in_sqlserver.html

1, what is a view?

2, why use a view;

3, order by in the view;

4, refresh the view;

5, update the view;

6, view options;

7, indexed view;

The view in SQL Server (above) should be asked to add the "Why should I use views" section in "View in SQL Server (above)".

5. Update the View

A view is a virtual table, and we are actually querying the underlying table when we query the view. A view can be used not only as a target for a select query, but also as a target for modifying statements. Of course, when you modify a view, it is a modification to the underlying table, which is like a proxy. Of course, you can limit the data you want to expose if you do not allow direct modification of the underlying table, only the view is allowed to be modified. In this way, you can protect your data, but this is a very limited amount of time.

So what are the constraints when updating the view?

1, you cannot insert data into a view as long as the view has a column that does not implicitly get the value, and if the column allows null, a default value, or a Idetity property, it can implicitly get the value;

2, if the view contains a junction, the update or INSERT statement affects only one end of the junction. In other words, the INSERT or UPDATE statement must define a list of target columns that can only be one end of the data junction. You cannot delete data from a view defined by a junction query;

3, you cannot modify the column that is the result of the calculation. For example: scalar expressions and aggregate functions, SQL Server does not attempt to change the results of the database engine calculations;

4, if the WITH CHECK option is specified when the view is created or modified, the INSERT or UPDATE statement that conflicts with the query filter for the view is rejected; I'll explain it in detail in the "View Options" section.

If the insert of trigger is defined on the view, data modification statements that violate these restrictions can be executed. In the insert of trigger you can replace the original modification with your own code;

When you allow changes to a view that has a join query definition, be cautious, such as a one-to-many relationship, if you modify the "one" end of a column value according to the "more" index value of the record, then the result is conceivable;

6. View Options

When you create or modify a view, you can specify options that the user controls the behavior and functionality of the view.

The encryption, schemabinding, and view_metadata options are specified in the view header, and the check option is specified after the query;

Such as:

   1:  VIEW v2

   2: With  encryption,schemabinding,view_metadata

   3:   as

   4: From  dbo. Orders

   5:  CHECK OPTION

1), encryption

This is a good option if you need to encrypt the view when you are building any kind of commercial software.

If the encryption option is not specified, SQL Server saves the user-defined statement as plain text, and if the encryption option is specified, the text of the object is confused.

SQL Server provides a system function sp_helptext the text of the view, and if the encryption option is applied, it gets "the text for object ' xx ' is encrypted" statement;

Note: Be sure to back up the view you want to encrypt before you encrypt it, and once you encrypt it, you can't go back.

2), SCHEMABINDING

If you create a view using the SCHEMABINDING option, SQL Server will not allow you to delete the underlying table or modify the referenced columns to prevent the view from becoming "orphaned" when the underlying object is modified, and if someone does not notice your view, the drop is executed, It is bad to delete columns or other actions referenced by the view. If you use the SCHEMABINDING option, you can avoid this situation.

If you want to create an index on a view, you must use the schmabinding option;

If you apply this option, you should note two points when defining the view:

1, all objects must consist of two-part names, such as: dbo should be used. Orders, not orders.

2, cannot use * in the select list, all column names must specify a name;

3), CHECK OPTION

Views created with check OPTION can prevent INSERT or UPDATE statements that conflict with the view query filter. Without this option, the view can accept modifications that do not conform to the query filter. Like what:

We created a Customwithorder view in the Northwind database, and now we have not added the WITH CHECK option

   1:  VIEW customerwithorder

   2: With  view_metadata

   3:   as

   4:  

   5:  

   6:  EXISTS (WHERE orders.customerid = customers.customerid) 

   7:  

The purpose of this view is to query the ID and company name of all customers with orders, and then we insert a nonexistent user ID and company name into the view:

   1:  VALUES (' MYSQL ',' myreed ')  

execution succeeds, and then in querying this customerwithorder view, it is clear that the query is not CustomerID for ' MySQL ' users, because the view contains only the users who have made the order, and if you query the Customers table directly, You will find this new user information.

Next, add the WITH CHECK option to the Customerwithorder view

   1:  VIEW customerwithorder

   2: With  view_metadata

   3:   as

   4:  

   5:  

   6:  EXISTS (WHERE orders.customerid = customers.customerid) 

   7:  

   8:  CHECK OPTION

Then execute the following statement:

   1:  VALUES (' ilsql ',' myreed ')  

You will receive the following error:

MSG 550, Level A, State 1, line 2
An attempt to insert or update has failed because the target view or a view that the target view spans has specified with CHECK option, and one or more result rows of the operation do not conform to the CHECK option constraint.
Statement has been terminated.

4), View_metadata

The purpose of this option is to make the view look more like a real table. Without this option, the metadata returned to the client's API will be the data of the underlying table on which the view depends;

If the client wants SQL Server to send metadata information for the view, rather than metadata for the underlying table, you can specify this option when creating or modifying the view; listen to me slowly;

Assuming that the user has permission to operate on the view without permission on the underlying table operation, the user performs some action on the view, and if the VIEW_METADATA option is specified, the statement will fail with security, as long as the VIEW_ is specified Metadata then return to the client is the metadata of the view, not the underlying table. On the other hand, if the user attempts to modify the data through the view, which in turn conflicts with the check option defined on the view, this operation is only possible if it is submitted directly to the underlying table.

There is such a tool in SQL Server, in SqlServer2000, the Enterprise Manager is, if we insert a record into the view, for example, to a with CHECK The option option in the Customerwithorder view inserts an arbitrary consumer regardless of whether it exists or not, and turns on the action that tracking enterprise submits to SQL Server, and you will find that the operation actually submits the base table as the target, in time he violates check OPTION, will also succeed. In the case of SSMs in SQL Server2005, it will be different if you manually insert a record in the Modify view, stating that although the View_metadata and check option options are specified, it is inserted into the underlying table. You can keep track of the actions that are submitted to SQL Server (with SQL Servers Profiler). However, if you are working in a panel created by "Open View", you will fail, prompting:

You can trace the action submitted to SQL Server again, and you can see that he submits to the target object as a view;

Or that sentence: If the client wants SQL Server to send metadata information for the view, rather than the metadata for the underlying table, you can specify this option when you create or modify the view

Did you get it this time?

I personally conclude that as long as there is a view_metadata option it is necessary to add the check option, while the SCHEMABINDING option is best added to prevent your view from being "orphaned" and the schemabinding option in the indexed view must be added.

7. Indexed views

Without an index, the data in the view would not have any physical representation, and if indexed, the data in the view would be physically synchronized, and SQL Server would synchronize the indexed view when modifying the underlying table. But you can't sync the view content directly.

We know that creating indexes on a table can improve performance, same as in view, the first index created on a view must be a unique clustered index before other nonclustered indexes can be created.

Indexed views must use the SCHEMABINDING option and cannot reference other views, only the underlying tables and UDFs, whereas the underlying tables and UDFs must be referenced using two-part naming conventions (see the Schemabinding option in 5. View options).

In addition to performance, you might also use indexed views for other reasons, such as having a column in an underlying table that we want to enforce the uniqueness of the known values in that column, but allow multiple null values to occur, what we're going to think of is a unique constraint, But the unique would think that two null values are equal, then this has to give up, then what is the way?

In fact, we can use an indexed view to do this task, using an indexed view to filter all non-null data, then such an index will prevent duplicate known values from entering the underlying table, but allow multiple null, because NULL is not part of the unique index, when we insert data into the underlying table, To limit our data by using the unique index view to achieve the purpose of enforcing the uniqueness of known values in a column;

We can demonstrate that you first create an underlying table T2 and an indexed view V2:

   1:  int,col2 NVARCHAR (+))

   2:  

   3:  VIEW V2

   4: With  SCHEMABINDING

   5:   as

   6:  NULL;

   7: On  dbo. V2 (col1);

We then insert the following data into the T2 table:

   1:  VALUES (1,' 2 ') 

   2:  VALUES (1,' 3 ') 

   3:  VALUES (null,' 4 ')  

   4:  VALUES (null,' 5 ')  

So which one of the above 4 inserts will fail? The answer is 2. Finally, let's select the base table T2, see the implementation of the request we started?

   1: From  T2

Perform:

View in SQL Server (bottom) reprint