Advantages and disadvantages of SQL View

Inner join/left Join JOIN Join multiple tables

 

SQL Execution Plan/view the specific execution process of SQL statements, working principle Advantages and Disadvantages of SQL usage View

10:56:43 | category: SQL | Tag: | large or small subscription size

The advantages of using views in database development include:

1. You can access the subset of columns in the table. Some columns in the table are sensitive data that you do not want to see, such as user passwords and employee salaries. You can use views to hide these columns.

2. You can access a subset of the row in the table. If you do not want users to see Data unrelated to TA, you can filter the data in the where condition. For example, employees of the company's subsidiary company can only see the data of their colleagues, instead of displaying the employee information of other subsidiaries, the data is filtered and queried to form a view.

3. You can rename the column name. Some column name definitions in the table have no representative meaning, such as the column "abc". To let the user see the column name, you can rename the column name when creating the view.

4. You can quickly access data composed of two or more table connections. Sometimes you need to access a dataset composed of table connections. You can define the queried dataset as a view to help you quickly access the required data.

5. You can quickly read data sets returned by aggregate function operations. Sometimes the user needs to read the result set after complex operations using aggregate functions. It takes a lot of time to read the result set each time. In this case, the computed dataset can be used to form a view, you can avoid the consumption of each read operation.

In general, the use of views improves security while accelerating query speed.

 

Advantages and disadvantages of a view

When designing a program, you must first understand the advantages and disadvantages of the view, so that you can foster strengths and circumvent weaknesses. The view has the following advantages:

● Simplicity. A view not only simplifies users' understanding of data, but also simplifies their operations. Frequently Used queries can be defined as views, so that you do not have to specify all the conditions for subsequent operations.

● Security. Users can only query and modify the data they can see through the view. Other data in the database is neither visible nor accessible. Database authorization commands allow each user to restrict the retrieval of a database to a specific database object, but cannot authorize the database to a specific row or column. Through views, users can be restricted to different subsets of data.

● Logical Data independence. View can make applications and database tables independent to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view.

View also has some disadvantages, mainly as follows.

● Performance: SQL Server must convert View queries to basic table queries. If this view is defined by a complex multi-Table query, even if it is a simple View query, it also takes some time for SQL Server to turn it into a complex combination.

● Modify restrictions: when you try to modify certain rows of a view, SQL Server must convert it to modify certain rows of the basic table. For a simple view, this is very convenient, but for a complicated view, it may not be modifiable.

Therefore, when defining database objects, you must define the view selectively. You should weigh the advantages and disadvantages of the view and reasonably define the view.