Features and optimization of visual charts in SQL Server

In the use of SQL Server, views are inevitable and frequently used. So what is a view? In summary, a view is an SQL statement written by the user. Let's name the SQL statement in this section, which is called the view name. If you want to use the result set returned by the SQL statement in this section, you only need to write an SQL statement for the view name like a query table. Therefore, you can also think of a view as a virtual table.

The following describes how to create and use a view:

I. Create a view:

Create view v_viewname as select * From Table1

View is the view keyword, v_viewname is the view name we created, and select * From Table1 is the result set returned by this view.

Ii. Call view: Same as querying a table

Select * From v_viewname

3. Delete A View:

Drop view v_viewname

From the above operations, we can see that after a view is created, the operations on it are the same as the operations on the object table.

View features: 1. view focuses on specific data. View allows users orProgramDevelopers only view the data they need, instead of exposing all the information and fields in the table, which enhances data security.

2. Simplified data operations and easy maintenance. We can define the commonly used multi-table joint query data or a specific result set as a view, which plays a role in modular data. When using this data, we can directly query this view without having to write long SQL statements everywhere, which also plays a role of easy maintenance.

3. The view can only query data. For example, for different users, we only provide some data to them. In this way, we can limit the result set in the view and return it to the view. In this way, no matter how the user defines the query conditions for the view, he cannot query the data that we do not want to provide to him.

View optimization: although a view can bring us various conveniences, it does not mean we can abuse it. Because a view is an SQL statement, its results are dynamically generated during each call. If an unreasonable view is defined, performance loss will inevitably occur.

The following are some notes for creating a view: 1. the operation view is slower than directly operating the basic table, so we try to avoid creating a view on a large table.

Second, try not to create nested views, that is, use the views in the Views. In this way, the basic table will be accessed multiple times during query, resulting in performance loss.

3. Try to return only the required information in the view, and try not to use tables that do not need to be accessed in the view.

4. stored procedures can be used instead of large tables or complex-defined views.

5. frequently-used views can be replaced by indexed views.