Advantages and disadvantages of using SQL Server views

SQL Server View we often use the following to introduce you to the advantages and disadvantages of using SQL Server view, you want to have more knowledge of your SQL Server view.

You must understand the pros and cons of the view before you design it, and the SQL Server view has some of the following advantages:

Simplicity. Views not only simplify the user's understanding of the data, they can also simplify their operations. Those queries that are used frequently can be defined as views, so that users do not have to specify all the conditions for subsequent operations each time.

Security Users can only query and modify the data they see through the view. Other data in the database is neither visible nor can be taken. The database Authorization command enables each user to limit the retrieval of a database to a specific database object, but not to a specific row or column on a database. With views, users can be limited to different subsets of the data.

Logical data independence. Views enable applications and database tables to be somewhat independent. If there is no view, the application must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view.

The SQL Server view also has some drawbacks, mainly as follows.

Performance: SQL Server must turn the query of the view into a query for the base table, and if the view is defined by a complex multi-table query, then even a simple query of the view, SQL Server will turn it into a complex combination that takes some time.

Modify limit: When a user attempts to modify some rows of a view, SQL Server must convert it to some row of the base table. This is convenient for simple views, but it can be non-modifiable for more complex views.

Therefore, when defining a database object, you cannot define the SQL Server view without selecting it, you should weigh the advantages and disadvantages of the view and define the view reasonably.

  Database design, you often encounter a decision: whether to use a view or an intermediate table?       Consider a scenario for inventory management: The most common document is warehousing and outbound orders, and the librarian needs to see the current inventory. To deal with the inventory, we have two scenarios: one is to use the view, all the warehousing minus all the out of the library, is the current inventory; the other is to use the intermediate table, create an inventory table, record the current inventory.      1, when using the view scheme       warehousing, the system records the inbound document;       Out of the library, the system queries the inventory view, determine whether there is sufficient inventory can be out of the library, and then record the library document;      visible, the system only need to record the inbound and outbound documents, Inventory is calculated by the DBMS in the query view,      2, using the Inventory table scheme       storage, the system records the inbound documents, At the same time increase the corresponding inventory;     , the system queries the inventory table, determine whether there is sufficient inventory can be out of the library, and then record the library documents, reduce the corresponding inventory;       can be seen, the system in addition to recording inbound and outbound documents, but also need to update the inventory table of the current inventory quantity;      3, scheme comparison       For the design and coding of the system itself, the view scheme is easy to implement, the test is convenient, and the inventory table scheme is slightly complicated. From this point on, the view scheme can be used in the prototype stage.       the performance of the user experience, the performance pressure of the view scheme is on the query inventory, the performance pressure of the inventory table scheme is:      on the business process. View scenario: Because each time the inventory is queried, the DBMS needs to scan the inbound and outbound documents for a long time, and may lock the inbound and outbound documents, resulting in an extension of inbound and outbound processing, or even failure (especially when querying inventory views in one transaction); Inventory view if connected to other tables or views, When a complex SQL is formed, the query is slower because the index is not valid (or unusable);      Inventory Table scheme: The inventory table can be indexed, the query speed than the view will be much faster; when you store and exit, the SQL that updates the inventory table has some impact on the speed at which the library is processed, but because the update affects only the SKUs that are in storage, With the Query inventory table concurrency, lock time is very short, the impact will be relatively small.      4, conclusion       View scenarios: Prototypes, smaller data volumes;       Inventory Table Scenario Application scenario: The data volume is large, the analysis of inventory is more;

Advantages and disadvantages of using SQL Server view