Let's talk about the Views in SQL Server and SQL Server.

Let's talk about the Views in SQL Server and SQL Server.

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;

1. What is a view?

A view is a virtual table defined by a query. Unlike a physical table, the data in the view has no physical representation unless you create an index for it; if you query a view without indexes, SQL Server actually accesses the base table.

If you want to create a view, specify a name and query for it. SQL Server only saves the view metadata. You can describe this object, as well as the columns, security, and Dependencies it contains. When you query a view, SQL server uses the view definition to access the basic table whether to obtain or update data;

Views also play many important roles in our daily operations. For example, you can use views to access filtered and processed data, rather than directly accessing basic tables, the basic table is also protected to a certain extent.

When creating a view, we must follow three rules:

Order by cannot be specified in the view definition, unless the definition contains the Top or For Xml description;
All columns must have column names;
All these column names must be unique;

The Order by statement is not allowed when top or for xml is not described in the view chart. This is because the view is considered a table and the table is a logical entity, its rows are unordered. All columns in the view must have column names, which are unique;

The following SQL statement creates a simple view:

Copy codeThe Code is as follows: create view dbo. V1
AS
SELECT CustomerID, CompanyName FROM MERs
Where exists (SELECT * FROM Orders WHERE MERs. CustomerID = Orders. CustomerID)

2. Why use View (update)

Since SqlServer provides us with such an object, it must have a role. However, when using a view, we either use too much or use less. Therefore, some people suggest not use the view, while some people suggest using less. Who should we listen?

In fact, if we master the purpose of using a view, we can use the correct view in the right place. What problems can a view solve for us?

1) to reduce the complexity of database presentation for end users. As long as the client writes simple code to the view, it will be able to return the data I need, and some complicated logic operations will be completed in the view;

2) To prevent sensitive columns from being selected and provide access to other important data;

3) add some additional indexes to the view to improve the query efficiency;

In fact, the view does not change anything, but only filters the accessed data in some form. Consider the role of a view. You can see how the view concept simplifies data for inexperienced users (only displays the data they care about), or does not allow users to access basic tables.
Rights, but grant them the right to access a view that does not contain sensitive data, thus hiding sensitive data in advance.

You know, by default, the view does not do anything special. The view runs from the command line like a query (there is no pre-optimization in any form), which means an additional overhead is added between the data request and the data to be delivered. This indicates that the view cannot be like
It is just as fast as running the underlying SELECT statement. However, there is a reason for the view-that is, its security or simplified for users, balancing your needs and overhead to find the most suitable solution for specific situations.

3. order by in the view

A view represents a logical entity, which is very similar to a table;

If we add an Order BY statement in the SQL statement created above, let's see what the effect is:

Copy codeThe Code is as follows: alter view dbo. V1
AS
SELECT CustomerID, CompanyName FROM MERs
Where exists (SELECT * FROM Orders WHERE MERs. CustomerID = Orders. CustomerID)
Order by CompanyName

Running this statement will fail and the following prompt will be returned:

Msg 1033, Level 15, State 1, Procedure V1, Line 5
Unless TOP or for xml is also specified, the order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions.

As prompted, ORDER By cannot be used either. order by can be used only after Top or for xml statements are specified, for example:

Copy codeThe Code is as follows: alter view dbo. V1
AS
Select top (10) CustomerID, CompanyName FROM Customers
Where exists (SELECT * FROM Orders WHERE MERs. CustomerID = Orders. CustomerID)
Order by CompanyName

However, it is not recommended to use order by in the view because the view represents a table, but there is no sorting for the table. Therefore, when querying the view, use order;

The SQL Server2005 online series provides the following description: "The ORDER BY clause is used in the definitions of views, inline functions, derived tables, or subqueries. The clause can only determine the rows returned BY the TOP clause. Order by does not guarantee that the ordered results are obtained when these structures are queried, unless order ."

4. Refresh the view

As I said above, the view will save metadata, columns, security, dependencies, and other information. If we change the architecture of the basic table, it will not be directly reflected in the view; after the architecture is changed, it is a good habit to use sp_refreshview stored procedure to refresh the view metadata;

For example, we created a table T1 and a T1 view V1, then changed T1, and then looked at the V1 results:

Create Table T1 first:

Copy codeThe Code IS as follows: IF OBJECT_ID ('t1') IS NOT NULL
Drop table T1
Create table T1 (col1 INT, col2 INT)
Insert into T1 (col1, col2) VALUES (1, 2)
GO

Then create the T1 view V1:

Copy codeThe Code is as follows: create view V1
AS
SELECT * FROM T1

In practice, avoid using * in the SELECT statement in the view. This is only a demonstration. If you query view V1, the following results are displayed:

Next, we will add a column col3 to table T1:

Copy codeThe Code is as follows: alter table T1 ADD col3 INT

Then you can query view V1 again. Do you want to query three columns or columns? The answer is two columns. The change in T1 architecture does not affect the view metadata. At this time, if we need to refresh the view V1, we can use the EXEC sp_refreshview V1 command to query it again, the V1 result is three columns.

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), the column cannot be modified as the calculation result. 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:

Copy codeThe Code is as follows: create view v2
With encryption, SCHEMABINDING, VIEW_METADATA
AS
SELECT OrderID FROM dbo. Orders
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.

Copy codeThe Code is as follows: create view CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers. CustomerID, Customers. CompanyName FROM Customers
Where exists (SELECT 1 FROM Orders WHERE Orders. CustomerID = Customers. CustomerID)

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:

Copy codeThe Code is as follows: insert into CustomerWithOrder (CustomerID, CompanyName) VALUES ('mylock', 'mylock ')

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.

Copy codeThe Code is as follows: alter view CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers. CustomerID, Customers. CompanyName FROM Customers
Where exists (SELECT 1 FROM Orders WHERE Orders. CustomerID = Customers. CustomerID)
WITH CHECK OPTION

Then execute the following statement:

Copy codeThe Code is as follows: insert into CustomerWithOrder (CustomerID, CompanyName) VALUES ('ilsql', 'mylock ')

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:

Copy codeThe Code is as follows: create table T2 (col1 INT, col2 NVARCHAR (50 ))
Create view V2
WITH SCHEMABINDING
AS
SELECT col1 FROM dbo. T2 WHERE col1 is not null;
Create unique clustered index idx_col1 ON dbo. V2 (col1 );

Then we insert the following data into Table T2:

Copy codeThe Code is as follows: insert into t2 (col1, col2) VALUES (1, '2 ')
Insert into t2 (col1, col2) VALUES (1, '3 ')
Insert into t2 (col1, col2) VALUES (null, '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?

Copy codeThe Code is as follows: SELECT * FROM t2

Run: