Shulou(Shulou.com)05/31 Report--

This article mainly introduces the limitations of SQL Server view management, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Constraint 1: changes to view data

When the user updates the data in the view, what is actually changed is the data of their corresponding data table. The same is true whether you make changes to the data in the view, or insert or delete data in the view. However, not all views can be changed. Such as the following views, you cannot update their contents directly in the SQLServer database, otherwise, the system will reject this illegal operation.

For example, in a view, if the GroupBy clause is used, the contents of the view are summarized. Users will not be able to update this view. This is mainly because after using the GroupBy clause to summarize the query results, the physical storage location of the record is lost in the view. In this way, the system cannot find records that need to be updated. If the user wants to change the data in the view, the database administrator cannot add this GroupBY grouping statement to the view.

If you cannot use the Distinct keyword. The purpose of this keyword is to remove duplicate records. If this keyword is not added, the view queries out 250 records. When this keyword is added, the database removes duplicate records and displays only 50 records that do not repeat. At this point, if the user wants to change one of the data, the database does not know which record it needs to change. Because there appears to be only one record in the view, there may be dozens of records in the underlying table. For this reason, if you use the Distinct keyword in the view, you cannot make changes to the contents of the view.

If you have AVG, MAX, and other functions in the view, you cannot update them. For example, when a view uses the SUN function to summarize employees' salaries, the table cannot be updated at this time. This is a constraint added by the database to ensure data consistency.

It can be seen that although the attempt is convenient and safe, it still can not replace the status of the table. When we need to update the data in some tables, we tend to do it more through the operation of the table. Because you need to comply with some restrictions if you make direct changes to the content of the view. In practice, more processing rules are to change the background basic table directly through the foreground program. As for the security of the data in these tables, it depends on the foreground application to protect it. Ensure the accuracy and legality of the changes.

Constraint 2: certain keywords cannot be used in query statements that define views

As we all know, a view is actually a set of query statements. In other words, a view is a tool for encapsulating query statements. In the query statement, we can format the displayed results with some keywords. For example, in our daily work, we often need to merge the data in one table with another. At this point, the database administrator can use the SelectInto statement to do this. Query the data from a table and then add it to a table.

When a similar operation is often needed, whether we can make it into a view. You only need to run this view every time you need it, instead of rewriting the SQL code every time. Unfortunately, the result is negative. In the view of the SQLServer database, you cannot have the Into keyword. If a similar function is to be achieved, it can only be achieved through a function or procedure.

In addition, unlike Oracle databases, there is an additional limitation when creating views in Microsoft's SQLServer databases. You just cannot use the orderby sort statement in the query statement that creates the view. This is a very special rule. Some Oracle database administrators often make similar mistakes when using SQLServer databases to create views. They don't understand why it works in Oracle databases, but not in Microsoft databases, a question that only the designers of Microsoft database products can answer. In short, you should remember that in the SQLServer database, when creating a view, the query statement cannot contain the OrderBy statement.

Restriction 3: alias some columns and ensure that the column names are unique

When you associate a query with a table, you only need to add the prefix of the table when the column names of different tables are the same. There is no need to name the column separately. However, a problem occurs when the view is created, and the database prompts the "duplicatecolumnname" error warning the user that there are duplicate column names. Sometimes, users use the Select statement to join multiple columns from different tables, and if they have the same name, the statement can still be executed. However, if you copy it to the window where the view is created, it will not succeed when you create the view.

There are many similar differences between a query statement and a query statement that creates a view. For example, sometimes, we may use some arithmetic expressions more frequently in query statements, or use functions in query statements, and so on. When querying, we do not have to "name" this column. The database will automatically name it when it is queried. However, when creating views, the database system will give you a problem. The system will remind you to give the column an alias.

From the above two examples, we can see that although the view is an encapsulation of the SQL statement, there is still a difference between the two. The query statement that creates the view must follow certain restrictions. If you want to ensure that each column name of the view is unique; if a column in the view is an arithmetic expression, function, or constant, give it a name, and so on.

Constraint 4: double restrictions on permissions

In order to ensure the security of the basic table data, the permission control is relatively strict when the view is created.

On the one hand, if users need to create views, they must have permission to create database views. This is a basic condition that must be followed when creating a view. For example, although some database administrators have the permission to create and modify tables, this does not mean that the database administrator has the right to create views. On the contrary, in large database design, database administrators are often divided into different groups. Those who set up the base table only set up the base table; those responsible for creating views only have the right to create views.

Second, while having the right to create a view, the user must also have access to the corresponding table. For example, a database administrator already has permission to create views. At this point, if it needs to create a view of employee salary information, it may not be successful. This also requires the database administrator to have access to the underlying tables related to salary information. If the view of creating employee salary information involves a total of five tables, the database administrator needs the owner's query permission for each table. If not, the creation of this view will fail.

The third is the inheritance of view permissions. As in the example above, the database administrator is not the owner of the underlying table. However, with the authorization of the owner, he can access the underlying table and build a view based on it. However, has the database administrator re-authorized access to this basic table to other people? for example, can he authorize user A to access the employee attendance information table? the answer is not necessarily. By default, database administrators can no longer authorize other users. However, if the owner of the underlying table gives this right to the database administrator, he can reauthorize the user. Allows the database administrator to authorize user A to perform related operations.

It can be seen that although the view is flexible, safe and convenient, it still has many restrictions. According to the author's experience, generally in the work of reports, forms and so on, the use of views will be more reasonable. Because its SQL statement can be reused. On the other hand, in the update of the basic table, including the change, deletion or insertion of the record, the basic table is often updated directly. Some table constraints can be implemented through triggers, rules, and so on, or even directly through foreground SQL statements. As a database administrator, you should have the ability to determine when to use the view and when to call the underlying table directly.

Thank you for reading this article carefully. I hope the article "what are the restrictions on SQL Server View Management" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.