T-SQL query advanced-Understanding SQL Server overwrite, join, cross and filter non-clustered Indexes

Before: This is the first T-SQL query advanced seriesArticleBut the advanced series of T-SQL query is far from finished. I think this topic needs some advanced knowledge as the foundation. If there are errors in this article, please do not correct it. The content of this article requires the indexing knowledge as the basis.

 

Introduction

In SQL Server, non-clustered indexes can be considered as a table containing clustered indexes. but compared to the actual table. the number of columns in tables stored in non-clustered indexes is much narrower, because non-clustered indexes only contain columns of non-clustered indexes in the original table and pointers to the actual physical table.

In addition, for non-clustered index tables, the columns in the tables are stored according to the clustered index. Therefore, the search speed is much faster. However, for performance extraction, SQL Server Always tries its best to obtain the required data on the leaf node of B if it only uses indexes, instead of using the pointer on the leaf node to find the actual physical table, the performance improvement will be better.

Below we will look at several ways to achieve this.

 

Coverage of non-clustered Indexes

As described above. Non-clustered indexes can be considered as a clustered index table. when the non-clustered index contains all the information required for the query, the query does not need to query the basic table, but only obtains data from the non-clustered index:

The following describes how to overwrite non-clustered indexes:

In the salesorderheader table of adventureworks, only the customerid column has a non-clustered index while the billtoaddressid does not have an index. Our query plan will be as follows:

The query finds the corresponding pointer based on the non-clustered index on the customerid column and searches for data in the basic table. As you can imagine from the execution plan, this efficiency is not fast.

Next we will look at the covered index. By creating a non-clustered index on customerid and billtoaddressid, We will overwrite all the data in the preceding query statement:

By overwriting indexes, we can see that the execution plan is simple enough to be no longer simple. We can directly extract data from leaf nodes with non-clustered indexes without looking for basic tables!

The performance improvement can be seen from IO statistics. Here we can see the IO comparison between covered indexes and no covered indexes:

Index coverage not only improves efficiency, but also improves concurrency. because it reduces the dependency on basic tables, it increases concurrency and reduces deadlocks!

 

Understanding the magic of include

The efficiency improvement brought by the above index coverage is just like magic, but don't worry, as I have mentioned in the entire article, everything has price. if an index contains too many keys, it will also bring many side effects. The function of include allows non-clustered indexes to contain more columns, but is not used as a "key.

For example, if we need to add a column for the above query, the original index cannot be overwritten, so we also need to find the basic table:

However, if you want to include the total amount of subtotal, the index is too wide, because our business rarely uses the order price as the query condition, then use include to create an index:

We can understand the columns included in the include statement and the columns created in the index. We can imagine the non-clustered indexes with the include statement as follows:

Use include to reduce the leaf "key" size!

 

Crossover of non-clustered Indexes

The cross view of non-clustered indexes is regarded as an extension that covers the index!

For many reasons, such:

• In the production environment, we often do not arbitrarily change the existing index as we set up the covered index above. This may result in more frequent customer calls to "take care"
• The existing non-clustered indexes are already very wide. If you continue to expand, the cost of performance reduction caused by the addition and modification of queries will be higher than the query benefit.

In this case, you can create additional indexes. As I mentioned earlier, the essence of non-clustered indexes is tables. By creating additional tables, several non-clustered indexes can be joined like tables, so that non-clustered indexes can be joined to provide the query optimizer with the required data without accessing the basic table:

For example, in the above example, we need to query the salesorderheader table and use billtoaddressid and customerid as the selection condition. We can overwrite the table by setting up two indexes. Let's look at the execution plan:

Non-clustered index connections

The non-clustered index connection is actually a special case of non-clustered index crossover. After multiple non-clustered indexes are crossed, the data to be queried can be overwritten, so that the basic table can be changed from reducing the number of queries to no need to query the basic table:

For example, if the above two indexes are used, I will query only the data contained in the non-clustered index, and I do not need to query the basic table at all:

 

Non-clustered index Filtering

In many cases, we do not need to index all the data in the index column of the basic table. For example, if a null value is contained, we do not want to be indexed, or according to the specific business scenario, we do not want to index some data. You can:

• Reduce index size
• The index is reduced to accelerate the query of the index.
• Small indexes provide higher maintenance performance for addition, deletion, and modification.

For example, the following statement:

After we create a clustered index for it:

In this case, we add filter conditions to form a filter index:

From the above we can see that the use of the filter index scenario should be related to specific business scenarios, for a large number of identical query conditions to create a filter index to further improve performance!

 

Summary

This article describes the covering, connection, crossover, and filtering of non-clustered indexes in SQL Server. The performance improvement we extract from SQL Server from each point is often accompanied by another sacrifice. As a database developer or administrator, it is very important to weigh the database with comprehensive knowledge. the knowledge of the system to learn databases can not only greatly reduce the amount of data read by logic, but also reduce the number of times customers call "Take care :-)