T-SQL query Advanced--Understanding the overlay, connection, crossover, and filtering of non-clustered indexes in SQL Server

Write in front: This is the first T-SQL query Advanced series article. But the T-SQL query step-up series is far from finished. This topic is advanced and I think it is because this topic requires some advanced knowledge as the basis. If there is any mistake in the article, please do not hesitate to correct me. The content of this article needs to be indexed knowledge as the basis.

Introduction

In SQL Server, a nonclustered index can actually be thought of as a table with a clustered index. But compared to the actual table. The number of columns stored in a nonclustered index is much narrower because the nonclustered index contains only the columns of the non-clustered index of the original table and pointers to the actual physical table.

Also, for a nonclustered index table, the columns that are stored are stored according to the clustered index. So the search speed is much faster. But for the performance squeeze, SQL Server always does its best, if only by indexing the leaf nodes of the B-tree to get the required data, instead of using the pointer on the leaf node to find the actual physical table, the performance of the improvement will be better.

Let's look at a few ways to achieve this.

overrides for nonclustered indexes

As the previous introduction says. A nonclustered index can actually be thought of as a clustered index table. When this nonclustered index contains all the information needed for a query, the query no longer needs to query the base table, but only the nonclustered index to get the data:

Here's how nonclustered indexes are overwritten:

In AdventureWorks's SalesOrderHeader table, only the CustomerID column has a nonclustered index, and Billtoaddressid has no index, our query plan would look like this:

The query finds the data on the base table after it finds the appropriate pointer based on the nonclustered index on the CustomerID column. From the execution plan it can be imagined that this efficiency is not unpleasant.

Let's take a look at the overlay index, by creating a nonclustered index on CustomerID and BILLTOADDRESSID, we overwrite all the data in the query statement above:

By overwriting the index, you can see that the execution plan is simple enough to extract the data directly from the leaf node of the nonclustered index without needing to find the base table!

This performance improvement can be seen from the IO statistics, and below we see an IO comparison with an overlay index and no overwrite index:

The overlay of an index is not just an increase in efficiency, but also an increase in concurrency, because it reduces the reliance on the base table, so it increases concurrency, which reduces deadlocks!

understand the magic of include

The efficiency gains from the above index overlays are like magic, but don't worry, as I've emphasized throughout, everything has the price. If an index contains too many keys, it can also bring many side effects. The role of include allows more columns to be included in a nonclustered index, but is not used as a "key."

For example, suppose that the query above requires a column to be added, the originally established index cannot be overwritten, and the underlying table needs to be found:

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 criteria, then use include to index:

Understanding the columns and indexes established by the include contains a column that you can think of as a non-clustered index that contains include:

Use include to reduce the size of the leaf "key"!

intersection of nonclustered indexes

The intersection of nonclustered indexes is seen as an extension of the overlay index!

For a number of reasons, such as:

• In a production environment, we tend not to alter the existing index as easily as the overlay index above, which may result in more frequent calls from customers to "take care"

• Existing nonclustered indexes are already "wide", and if you continue to widen, the cost of the performance degradation associated with incremental changes is higher than the benefits of query.

At this point, you can create an additional index. As I mentioned earlier, the essence of a nonclustered index is a table, with additional tables to make table-like joins between several nonclustered indexes, so that a join can be made between nonclustered indexes to provide the query optimizer with the required data without accessing the base table:

For example, or the above example. We need to check the SalesOrderHeader table, with Billtoaddressid,customerid as the selection criteria, you can build two indexes to cover, let's take a look at the execution plan:

connections to nonclustered indexes

A non-clustered index connection is actually a special case of a cross of a nonclustered index. When multiple nonclustered indexes intersect, you can overwrite the data you want to query, making it possible to change the base table from a reduced query to an entirely non-query base table:

For example, the two indexes above, when I query only the data contained in the nonclustered index, I no longer need to query the base table at all:

filtering for nonclustered indexes

Many times, we do not need to index all the data in the index column of the base table, for example, null values do not want to be indexed or, depending on the business scenario, there is some data that we do not want indexed. This can:

• Reduce the size of the index

• Indexes are reduced, resulting in faster queries to the index

• Small index for adding and removing changes in the maintenance performance will be higher

For example, the following statement:

After we have set up a clustered index for it:

At this point we add filter conditions to form a filtered index:

From the above we can see that the use of filtered index of the scene to be related to the specific business scenario, for a large number of the same query conditions to establish a filtered index to further improve performance !

Summary

This article describes the overlay, connection, crossover, and filtering of non-clustered indexes in SQL Server. For each of us, the performance gains from SQL Server are often accompanied by sacrifices on the other. As a database developer or manager, it is important to weigh the tradeoffs with comprehensive knowledge. The knowledge of the system's learning database can not only reduce the data of logical reading, but also reduce the number of "care":-)

T-SQL query Advanced--Understanding the overlay, connection, crossover, and filtering of non-clustered indexes in SQL Server