Four constraints to be observed in SQL Server View Management

Access data through views has obvious advantages. For example, data is kept confidential, data logic independence is ensured, and query operations are simplified.

However, in other words, the views in the SQL Server database are not omnipotent, and there is a major difference between them and the basic object of tables. When using a view, you must comply with four restrictions.

Restriction 1: Changes to view Data

When a user updates the data in the view, the data in the corresponding data table is actually changed. It is similar to changing the data in the view or inserting or deleting data in the view. However, not all views can be changed. The following views cannot be directly updated in the SQL Server database. Otherwise, the system rejects such illegal operations.

For example, if the Group By clause is used in a view, the content in the view is summarized. You cannot update this view. This is mainly because after the query results are summarized By the Group By clause, the physical storage location of this record is lost in the view. In this way, the system cannot find the record to be updated. If you want to change data in the view, the database administrator cannot add the Group BY statement to the view.

If the Distinct keyword cannot be used. This keyword is used to remove duplicate records. If this keyword is not added, 250 records are queried by the view. After this keyword is added, the database removes Repeated Records and only displays 50 records that are not repeated. If you want to change one of the data records, the database does not know which records need to be changed. Because there seems to be only one record in the view, and there may be dozens of records in the basic table. Therefore, if the Distinct keyword is used in the view, the content in the view cannot be changed.

If a view contains functions such as AVG and MAX, it cannot be updated. For example, if a view uses the SUN function to collect employees' salaries, this table cannot be updated. This is a restriction added by the database to ensure data consistency.

It can be seen that although it is convenient and secure, it cannot replace the table status. When we need to update the data in some tables, we usually perform more operations on the tables. If you directly change the view content, you must comply with some restrictions. In actual work, more processing rules are to directly change the basic background table through the foreground program. As for the data security in these tables, we need to rely on front-end applications to protect the data. Ensure the accuracy and validity of changes.

Restriction 2: certain keywords cannot be used in query statements defining views.

As we all know, a view is actually composed of a set of query statements. In other words, a view is a tool that encapsulates query statements. In the query statement, we can use some keywords to format the display results. For example, in our daily work, we often need to merge the data in a table with another table. In this case, the database administrator can use the Select Into statement. First, the data is queried from a table and then added to a table.

When similar operations are often required, can we make them into a view. You only need to run this view every time you need it, instead of re-writing the SQL code every time. Unfortunately, the result is no. In the SQL Server database view, it cannot contain the Into keyword. To implement similar functions, you can only implement them through functions or procedures.

In addition, unlike Oracle databases, there is an additional restriction when creating views in Microsoft SQL Server databases. The order by sorting statement cannot be used in the query statement for creating a view. This is a special rule. Some Oracle Database Administrators often make similar errors when using SQL Server databases to create views. They don't understand why Oracle databases are feasible, but Microsoft databases do not? I am afraid that only the designers of Microsoft database products can answer this question. In short, we should remember that when creating a view in the SQL Server database, the query statement cannot contain the Order By statement.

Condition 3: aliases must be obtained for certain columns and the column name must be unique.

During table join query, when the column names of different tables are the same, you only need to add the table prefix. You do not need to name the column separately. However, when creating a view, a problem occurs. The database prompts a "duplicate column name" error and warns you of duplicate column names. Sometimes, you can use the Select statement to connect multiple columns from different tables. If you have the same name, this statement can still be executed. However, if you copy it to the window for creating a view, it will fail to be created.

There are many similar differences between Query statements and query statements used to create a view. For example, we may frequently use arithmetic expressions in query statements, or use functions in query statements. During the query, we can leave this column "name" unspecified ". When a database is queried, it is automatically named. However, when creating a view, the database system will give you a problem. The system will remind you to take the alias for the column.

From the two examples above, we can see that although views encapsulate SQL statements, the two are still different. The query statement for creating a view must comply with certain restrictions. To ensure that each column name in a view is unique, if a column in a view is an arithmetic expression, function, or constant, name it, and so on.

Restriction 4: Dual Permissions

To ensure the security of basic table data, the permission control is strict when the view is created.

On the one hand, to create a view, you must have the permission to create a database view. This is a basic condition that a view must follow when it is created. For example, although some Database Administrators have the permission to create and modify tables, this does not mean that the database administrator has the permission to create views. On the contrary, database administrators are often divided in large database designs. To create a basic table, you only need to create a basic table. To create a view, you only need to create the view.

Second, you must have the permission to create a view and access the corresponding table. For example, a database administrator has the permission to create a view. At this time, if you need to create a view of employee salary information, it may not be successful. This requires the database administrator to have the access permission to the basic table related to salary information. If the employee salary information is set up, five tables are involved, the database administrator needs the query permission of each table of the owner. If not, the creation of this view will fail.

The third is the inheritance of view permissions. In the preceding example, the database administrator is not the owner of the base table. However, with the authorization of the owner, the user can access the basic table and create a view based on this. However, does the database administrator authorize other users to access the basic table? Can he authorize user A to access the employee attendance information table? The answer is not necessarily. By default, the database administrator cannot authorize other users. However, if the owner of the base table grants this permission to the database administrator, the user can be re-authorized. The database administrator can authorize user A to perform related operations.

It can be seen that although the view is flexible, secure, and convenient, it still has many restrictions. Based on the author's experience, it is more reasonable to use views in reports, forms, and other work. Because its SQL statement can be reused. Basic table updates, including record changes, deletions, or inserts, usually directly update the basic table. Some table constraints can be implemented through triggers, rules, and so on, or even directly implemented through front-end SQL statements. As a database administrator, you must be able to determine when to use the view and when to directly call the basic table.

1. How to batch import data in the SQL Server database
2. Actual memory consumption during SQL Server Query
3. Slow SQL Server Query Solution