T-SQL query advanced-deep view-zt

Document directory

•  
•  
•  

Http://www.cnblogs.com/CareySon/archive/2011/12/07/2279522.html Introduction

 

The view can be viewed as a virtual table defined on SQL Server. A view, like its name, is another entry to view data. the conventional view itself does not store actual data, but only stores a Select statement and metadata of the involved table.

A simple understanding of the view is as follows:

Through the view, the client no longer needs to know the structure of the underlying table and Its Relationship. The view provides an interface for unified data access.

 

Why View)

As a result, it is not difficult to find that using a view will bring the following benefits:

 

• The view hides the underlying table structure and simplifies data access operations.
• Because the underlying table structure is hidden, security is greatly enhanced. You can only view the data provided by the view.
• The use of views facilitates permission management, allowing users to have permissions on views instead of the underlying tables, further enhancing security.
• A view provides an interface for user access. When the underlying table changes, the view statements are changed to adapt, so that the client programs created in this view are not affected.

 

View Classification

View can be divided into three types in SQL

1. Regular View)
2. Indexed View)
3. Partition View)

The following describes the view types.

 

Rugular View)

A normal view is defined by a Select statement. A view only contains the metadata defined by it and referenced tables. It does not actually store data. The template for creating a view in MSDN is as follows:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ]<view_attribute> ::= {    [ ENCRYPTION ]    [ SCHEMABINDING ]    [ VIEW_METADATA ]     } 

There are still few parameters. Now let's explain the above parameters:

ENCRYPTION: The view is encrypted. If this option is selected, it cannot be modified. You need to save the script when creating the view, or you cannot modify it any more.

SCHEMABINDING: defines and binds the table referenced at the underlying layer. If this option is selected, the schema of the table referenced by the view cannot be changed (for example, the Data Type of the column). If you need to change the underlying table schema, drop or alter the view bound to the underlying table first.

VIEW_METADATA: this is an interesting option. as indicated by the name of this option, if this option is not selected, the metadata returned to the client is the metadata of the table referenced by the View. If this option is selected, the metadata of the View is returned. in other words, VIEW_METADATA makes the view look like a table. The definition of each column in the View directly tells the client, rather than the definition of the referenced underlying table column.

WITH Check Option: This Option is used to update data, which will be explained in the "update data through view" section.

 

Of course, in addition to complying with the preceding syntax rules, creating a view also has some rules to follow:

• In View, the Order By clause cannot be used unless the TOP keyword exists. (If you want to use Order by for a row, the hack here uses Top 100 percent .....)
• View must be unique in each Schema
• View nesting cannot exceed 32 layers (in fact, in actual work, whoever has more than two layers of nesting will be pushed to PP -.-)
• The Compute, compute by, and INTO keywords cannot appear in the View.
• The View cannot be created on a temporary table.
• View cannot query full-text indexes

 

A simple example of creating a View:

CREATE VIEW v_Test_View1ASSELECT TOP 100 * FROM HumanResources.Employee

 

After the view is created, you can access the view as you access the table:

SELECT * FROM v_Test_View1

 

In Management studio, I prefer this method when creating a view, which is much more convenient:

 

  Indexed View)

Before talking about the index view, I suddenly remembered a cartoon I had read, saying that we can't afford high-end products, but it's okay to save money to buy an IPhone and install high-end products:

 

 

In fact, the index view is similar. On the basis of a common view, a unique clustered index is created for the view, and the view becomes an index view. apply the formula of the cartoon above: View + clustered Index = index View

The index view can be viewed as an equivalent object to a Table!

The index View in SQL Server and the Materialized View in Oracle are concepts. To understand the index View, you must first understand the clustered index. Clustered indexes are simply understood as primary keys. The data in the database is physically stored in the table in the order of primary keys, just like the Xinhua Dictionary. The default value is ABCD .... Set the content in this way. ABCD .... This is equivalent to the primary key. This avoids full table scanning and improves performance. Therefore, a table can only have one clustered index.

For the index view, add a clustered index to the view. The view is not just the select statement and the metadata of the table. The index view will physically store the data in the database, and keep the data stored in the index view synchronized with the underlying table involved in the index view.

After understanding the principles of the index view, we can see that the index view will greatly improve the performance of OLAP-based massive data analysis and query. In particular, there are Aggregate functions in the index view, which involve a large amount of high-cost JOIN operations. Because the results of aggregate function compute are physically stored in the index view, when a large amount of data is used in the index view, it is not necessary to perform aggregation operations every time, which will undoubtedly greatly improve the performance.

At the same time, after the Update, Insert, and Delete operations are performed on the tables involved in the index view, SQL Server must identify the changed rows for the index view to synchronize data. therefore, for services such as OLTP that need to be added, deleted, and modified, a large number of synchronization operations are required for the database, which reduces performance.

After talking about the basic principles, benefits, and disadvantages of the index view, let's look at the implementation in SQL Server:

Implementing the index view in SQL Server is very simple. You only need to add a unique clustered index to the existing view. however, the restrictions imposed by SQL Server on the index view make many DBAs not favor them:

For example:

• The basic table involved in the index view must be set to ON
• You must set ANSI_NULLS and QUOTED_INDETIFIER to ON
• The index view can only reference basic tables.
• SCHEMABINDING must be set
• The full name such as Schema. ViewName must be used to define the index view.
• The index view cannot contain subqueries.
• Aggregate functions such as avg, max, min, stdev, stdevp, var, and varp cannot be used.

..................

More... I will not list them one by one. If you are interested, please Google it on your own.

Here is an example to illustrate the index View:

Assume that in the adventureWorks database, we have a query:

SELECT p.Name,s.OrderQtyFROM Production.Product p inner join Sales.SalesOrderDetail sON p.ProductID=s.ProductID

 

Execution Plan for this query:

At this time, I create a view and create a unique clustered index on this view:

-- Create view v_Test_IndexedViewWITH SCHEMABINDINGASSELECT p. name, s. orderQty, s. salesOrderDetailIDFROM Production. product p inner join Sales. salesOrderDetail sON p. productID = s. productIDGO -- create unique clustered index indexedview_test1ON v_Test_IndexedView (SalesOrderDetailID) on The View)

 

Next, let's use Liu Qian's line: It's time to witness the miracle. Let's execute the previous query again:

 

In the above example, we can see the powerful power of the index view. Even if your query statement does not contain this index view, the query analyzer will automatically select this view, this greatly improves the performance. of course, such powerful performance is available only in SQL server Enterprise Edition and Development edition (although I have seen many SQL Server developers asking the company to pay for the Enterprise Edition, using the functions of Express ......)

Partition View)

 

In fact, in terms of micro-implementation, the data returned by the entire view is composed of several parallel tables (both several tables have the same table structure, that is, columns and data types, but the row sets stored are different.) for the UNION join (if you do not know about the UNION join, please refer to my previous blog post), you will obtain the dataset.

The split view can be divided into two types:

1. Local Partitioned View)

2. Distributed Partitioned View)

Because the local split view is only intended to be backward compatible with versions earlier than SQL Server 2005, we only describe the distributed split view here.

The distributed split view is actually obtained by connecting several parallel datasets obtained from different data sources or the same data source. A simple concept is shown below:

 

The data obtained from the preceding view comes from three tables with different data sources. Each table contains only four rows of data, which eventually forms the split view.

The biggest advantage of using a distributed split view is to improve performance. for example, in the above example, I only want to obtain the information of the employee ContactID of 8. If the information is obtained through the distributed view, SQL Server can intelligently scan only table 2 containing ContactID 8, thus avoiding full table scanning. This greatly reduces IO operations and improves performance.

Note that the primary keys of the tables involved in the distributed split view cannot be repeated. For example, the preceding table A ContactID is 1-4, the ContactID of Table B cannot be 2-8.

Note that the Check constraint must be applied to the primary key of the distributed split index to let the SQL Server Query analyzer know which table to scan. The following is an example.

 

In the example database AdventureWorks of Microsoft, I store data from the first 100 rows and-rows in two tables respectively through ContactID: Employee100 and Employee200. The Code is as follows:

--create Employee100SELECT TOP 100 * INTO Employee100FROM HumanResources.Employee ORDER BY EmployeeID--create Employee200SELECT *  INTO Employee200FROM (SELECT TOP 100 *FROM HumanResources.Employee WHERE EmployeeID NOT IN (SELECT TOP 100 EmployeeID FROM HumanResources.Employee ORDER BY EmployeeID)ORDER BY HumanResources.Employee.EmployeeID)AS e

 

Create a distributed split view:

CREATE VIEW v_part_view_testASSELECT * FROM Employee100UNION SELECT * FROM Employee200

 

In this case, we can query this index:

 

SELECT * FROM v_part_view_testWHERE EmployeeID=105

 

The following is the execution plan:

It can be seen that through this split method, the execution plan only scans the Employee200, thus avoiding scanning all the data, which undoubtedly improves the performance.

Therefore, when you place different data tables on different servers or use RAID5 disk arrays, the distributed split view further improves query performance.

 

Can distributed split views improve performance in all circumstances? This method is not appropriate. If the query to be processed contains an aggregate function, especially the aggregate function also contains distinct. Or sorting without the where condition is definitely the performance killer. Because the aggregate function needs to scan all the tables in the distributed split view, and then perform the UNION operation before performing the operation.

 

Update data through View

I do not recommend using views to update data. Because views do not accept parameters, we recommend using stored procedures.

The method for updating data using View is exactly the same as that for updating data in Table (as mentioned earlier, View can be regarded as a virtual Table, and if it is an index View, it is a specific Table)

Note the following when updating data through views:

1. There must be at least one user table after the From clause in the view

2. No matter how many tables are involved in a View query, only the data of one of the tables can be updated at a time.

3. Columns computed by expressions, constant columns, and columns computed by Aggregate functions cannot be updated.

4. columns that cannot be affected By the Group By, Having, and Distinct keywords cannot be updated.

 

Here, there is a WITH Check Option for creating a View. If this Option is selected, the data updated through the View must meet the conditions defined by the where clause in the View, for example:

Create a View:

 

Tips in View

1. view definition can be found by view name

SELECT * FROM sys. SQL _modulesWHERE object_id = OBJECT_ID ('view name ')

 

 

 

2. as mentioned above, a common view only stores the select statement and the metadata of the referenced table. When the underlying table data changes, sometimes the metadata of the table in the view is not synchronized in time, you can use the following code for manual synchronization:

EXEC sp_refreshview view name

 

 

Best practices of View

This is my personal experience.

• Make sure to optimize the performance of the Select statement in the View (it seems to be nonsense, but the truth is nonsense ...)
• It is best not to nest a View. If you want to nest a View, only one layer can be nested.
• You can use stored procedures and user-defined functions to replace views. Do not use views as much as possible. Stored Procedures cache execution plans with better performance and fewer restrictions.
• In the split view, do not use aggregate functions. In particular, Aggregate functions also contain Distinct.
• In the view, if the Where clause can be added to the view, it is not added to the view (because calling the view will return all rows, and then filtering, performance killer, if you add order .....)

 

Summary

The three types of views are described in this article. Each view has its own scope of use. If used properly, the performance will be upgraded to a higher level. If used improperly, performance will be slowed down.

I think of a famous saying: "everything has price, always trade-off ".....