Detailed description of views in SQL Server (below)

 

1. What is a view?

2. Why view;

3. order by in the view;

4. Refresh the view;

5. Update the view;

6. view options;

7. Index View;

For more information, see the "Why use View" section in "View (top)" of SQL Server as required.

5. Update the view

A view is a virtual table. When querying a view, we actually query the basic table. A view can be used not only as a target for SELECT queries, but also as a target for modifying statements. Of course, when you modify a view, you modify the basic table as a proxy. Of course, if you do not allow direct modification of the basic table and only allow modification of views, You can restrict the data to be published. In this way, you can protect your data, but there are few such restrictions.

What are the restrictions for updating a view?

1. As long as a column in the view cannot obtain values implicitly, you cannot insert data into the view. If the column allows NULL, default values, or IDETITY attributes, it can obtain values implicitly;

2. If a view contains a join, the UPDATE or INSERT statement can only affect one end of the join. That is to say, the INSERT or UPDATE statement must define the target column list. These columns can only be one end of the data link. You cannot delete data from the view defined by the join query;

3. Columns cannot be modified as calculation results. For example, does not attempt to change the computing results of the database engine due to scalar expressions and Aggregate functions;

4. If the with check option is specified when a view is created or modified, the INSERT or UPDATE statements that conflict WITH the query filter of the view will be rejected; I will explain in detail in the "view options" section.

If an 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 modifications to views defined by join queries, be cautious, such as the one-to-many relationship, if you modify the record of a column value corresponding to "1" according to an index value of "multiple", the result can be imagined;

6. view options

You can specify some options when creating or modifying a view. These options allow you to control the view's behavior and functions.

The ENCRYPTION, SCHEMABINDING, and VIEW_METADATA options are specified in the view header, and the check option is specified after the query;

For example:

   1:  CREATE VIEW v2

   2:  WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA

   3:  AS

   4:  SELECT OrderID FROM dbo.Orders

   5:  WITH CHECK OPTION

1), ENCRYPTION

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

If the ENCRYPTION option is not specified, SQLSERVEr saves the User-Defined statements in plain text format. If the ENCRYPTION option is specified, the object text is obfuscated.

SQLSERVER provides a system function sp_helptext to view The view text. If The ENCRYPTION option is applied, The "The text for object 'xx' is encrypted" statement is obtained;

Note: You must back up the view you want to encrypt before encryption. Once encrypted, you cannot go back.

2), SCHEMABINDING

If you use the SCHEMABINDING option to create a view, SQLSERVER will not allow you to delete the base table or modify the referenced columns to prevent the view from being "isolated" when modifying the underlying object ", if someone has not noticed your view and executed DROP, delete the column referenced by the view or other operations, it would be terrible. If you use the SCHEMABINDING option, you can avoid this situation.

To create an index on a view, you must use the SCHMABINDING option;

If this option is applied, pay attention to two points when defining the View:

1. All objects must be composed of two parts. For example, dbo. Orders should be used instead of Orders.

2. * cannot be used in the SELECT list. A name must be specified for all column names;

3), CHECK OPTION

The view 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 comply with the query filter. For example:

Create a CustomWithOrder view in the Northwind database. The with check option is not added yet.

   1:  CREATE VIEW CustomerWithOrder

   2:  WITH VIEW_METADATA

   3:  AS

   4:   

   5:  SELECT Customers.CustomerID,Customers.CompanyName FROM Customers 

   6:  WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)

   7:   

This view is used to query the IDs and company names of all customers with orders. Next we insert a nonexistent user id and company name to the View:

   1:  INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('MYSQL','MyReed')

The execution is successful, and then the CustomerWithOrder view is queried. Obviously, the user whose CustomerID is 'mysql' cannot be queried because the view only contains the user whose order has occurred. If you directly query the Customers table, you will find the new user information.

Next, add the with check option to the CustomerWithOrder view.

   1:  ALTER VIEW CustomerWithOrder

   2:  WITH VIEW_METADATA

   3:  AS

   4:   

   5:  SELECT Customers.CustomerID,Customers.CompanyName FROM Customers 

   6:  WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)

   7:   

   8:  WITH CHECK OPTION

Then execute the following statement:

   1:  INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('ILSQL','MyReed')

You will receive the following error:

Msg 550, Level 16, State 1, Line 2
The attempt to insert or update failed because the with check option is specified for the target view or a view that is crossed by the target view, one or more result rows of the operation do not meet the check option constraint.
The statement has been terminated.

4), VIEW_METADATA

This option makes the view look more like a real table. If this option is not used, the metadata of the api returned to the client is the data of the basic table on which the view depends;

If the client wants SqlServer to send the metadata information of the view instead of the metadata of the basic table, you can specify this option when creating or modifying the view. Is it difficult to listen;

Assume that the user has operation permissions on the view, but does not have the permission to operate on the basic table, then the user performs some operations on the view. If the VIEW_METADATA option is specified, if VIEW_METADATA is specified, the returned data to the client is the view metadata instead of the basic table metadata. On the other hand, if you try to modify data through the view, and this operation conflicts with the check option defined on the view, this operation can be successful only when it is directly submitted to the basic table.

SqlServer has such a tool. In SqlServer2000, the Enterprise Manager is, if we insert a record into the view, for example, insert an arbitrary consumer into the CustomerWithOrder view WITH the check option, whether it exists or not, and open the tracing Enterprise Manager to submit the operation to SQL Server, you will find that the basic table is actually submitted as the target, and it will be successful if it violates the check option in time. In SSMS of SQL Server2005, it will be different. If you manually insert a record in the "Modify" view, it will be successful. Although VIEW_METADATA and CHECK OPTION are specified, it is still inserted into the basic table. You can track the operations submitted to Sqlserver (using SQL server Profiler ). However, if you perform operations on the panel generated by "Open View", the Operation will fail and the prompt is:

You can trace the operations submitted to SQL server again, and then you can see that the target object submitted to is a view;

If the client wants SqlServer to send the view metadata instead of the basic table metadata, you can specify this option when creating or modifying the view.

Do you understand this time?

 

In my personal summary, it is necessary to add the check option as long as the VIEW_METADATA OPTION is available, and the schemabinding option should also be added to prevent your view from being "isolated ", in the index view, the SCHEMABINDING option must be added.

7. Index View

If there is no index, the data in the view will not have any physical representation. If an index is added, the data in the view will be physically converted. SqlServer will synchronize the index view when modifying the basic table. However, you cannot directly synchronize the View content.

We know that creating an index on a table can improve the performance. The same is true for the view. The first index created on the view must be a unique clustered index, before creating other non-clustered indexes.

The index view must use the SCHEMABINDING option and cannot reference other views. Only basic tables and udfs can be referenced. Basic Tables and udfs must be referenced using two naming conventions (see Figure 5. SCHEMABINDING option in view options ).

In addition to performance, you may also use the index view for other reasons. For example, if there is a column in a basic table, we need to force the uniqueness of known values in this column, but what should we do if multiple NULL values are allowed? The first thing we think of is to use the UNIQUE constraint, but UNIQUE will think that the two NULL values are equal, so this has to be abandoned, what else can we do?

In fact, we can use an index view to complete this task and use the index view to filter all non-NULL data. This index will prevent repeated known values from entering the base table, but multiple NULL values are allowed, because NULL is not part of the UNIQUE index. When we insert data into the basic table, we use the UNIQUE of the index view to restrict our data, to enforce the uniqueness of known values in a column;

First, create a base table T2 and an index view V2:

   1:  CREATE TABLE T2(col1 INT,col2 NVARCHAR(50))

   2:   

   3:  CREATE VIEW V2

   4:  WITH SCHEMABINDING

   5:  AS

   6:  SELECT col1 FROM dbo.T2 WHERE col1 IS NOT NULL;

   7:  CREATE UNIQUE CLUSTERED INDEX idx_col1 ON dbo.V2(col1);

Then we insert the following data into Table T2:

   1:  INSERT INTO t2(col1,col2) VALUES(1,'2')

   2:  INSERT INTO t2(col1,col2) VALUES(1,'3')

   3:  INSERT INTO t2(col1,col2) VALUES(null,'4')

   4:  INSERT INTO t2(col1,col2) VALUES(null,'5')

Which of the above four INSERT statements will fail? The answer is 2. Finally, let's SELECT the base table T2 to see if we have started that requirement?

   1:  SELECT * FROM t2

 

Run:

Haha!

Sleepy, sleeping!

Xingbaofang

Tag: Update View, View options, index View, View, SQL Server View, encrypted View

References: T-SQL programming