SQL Server Index advanced: second-level, in-depth non-clustered Index

Original article: StairwaytoSQLServerIndexes: Level2, DeeperintoNonclusteredIndexes this article is part of the advanced series of SQL Server indexes (StairwaytoSQLServerIndexes. In Level 1

Original article: StairwaytoSQLServerIndexes: Level2, DeeperintoNonclusteredIndexes this article is part of the advanced series of SQL Server indexes (StairwaytoSQLServerIndexes. In Level 1

Original article address:

Stairway to SQL Server Indexes: Level 2, Deeper into Nonclustered Indexes

This article is part of the SQL Server Index advanced series (Stairway to SQL Server Indexes.

The first level introduces non-clustered indexes in SQL Server. In addition, in the first learning example, we have demonstrated the potential benefits of indexing when a row of data is obtained from a table. At this level, we will continue to introduce non-clustered indexes to see their contributions in improving query performance.

Let's first introduce some theories, learn some internal information about indexes, help us explain the theories, and then execute some queries. These queries are executed in two cases: including and without indexes. When performance reports are enabled, we can see the impact of indexes.

We continue to use some tables in the AdventureWorks database, mainly in the Contact table. We will just use an index and use the FullName index at the upper level to prove our point of view. To ensure that we have good control over the index of the Contact table, we will make two copies, one for creating the FullName index, the other for the VM, and the other for not creating the index.

Non-clustered Index

Create a non-clustered index in the Contacts_index table

Keep in mind that the index key is stored in a non-clustered index sequence and the real data in the table is accessed through tags. You can regard a tag as a pointer. In the future, the tag format, usage, and details will be described.

In addition, the SQL Server non-clustered index entry also contains some internal headers and optional data values. These will be introduced in subsequent articles and are not the key content.

So far, we only need to know that the key enables SQL Server to find the appropriate index entry, and the Entry Label allows SQL Server to access the row data of the table.

Benefits of orderly indexing entries

The index entries are ordered, so SQL Server can quickly locate the entries. Scanning can start from the header, from the tail, or from the middle.

Therefore, for a query, request all the Contact users whose LastName starts with S (where lastname like's % '). SQL Server quickly locates the records starting with the first S, and then uses the index to access data rows with tags until the records starting with the first T.

If the selected columns are included in the index, the above query will be executed faster. If we execute

SQL Server quickly navigate to the S entry, and then through the index, ignore the tag, directly return data from the index entry until the first T entry. In relational database terms, it is called full-coverage index query.

Many SQL operations can benefit from indexes, including ORDER BY, GROUP BY, DISTINCT, UNION (not UNION ALL), JOIN... ON.

Remember the importance of the key order from left to right. The index we created is very useful for lastname = "ashton", but it does not even work for firstname = "ashton.

Test some simple queries

If you want to execute the following query, make sure that you have executed the preceding script, created the contact_index and contact_noindex tables, and created the LastName and FirstName indexes in the contact_index table.

Enable statistics

Since there are only 19972 rows of data in the contact table, it is difficult to get a meaningful statistical time. Most Queries show CPU time: 0 ms, so we can disable time statistics and only display io statistics. If you need a large table to calculate the real time information, you can use the script later in the article to build a contact table with millions of rows of data. The following tests all take the tables of 19972 rows as test objects.

Test a fully-covered Query

The first query is a query that overwrites the index. It obtains some columns of records whose lastname starts with S in the contact table. The following is the execution information.

SQL statement SELECT FirstName, LastName
FROM dbo. Contacts -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE's %'

If no index is available (2130 row (s) affected)
Table 'contacts _ noindex'. Scan count 1, logical reads 568.

If there is an index (2130 row (s) affected)
Table 'contacts _ Index'. Scan count 1, logical reads 14.

The index impact I/O has been reduced from 568 to 14.

It is a good thing to overwrite the query index with annotations. If no index exists, a full table scan is performed. Row 2130 indicates that records starting with S account for 10% of the data.

Test A non-fully covered Query

Let's modify the query to make the same query, but the obtained column contains some columns without index creation. The following is the execution result.

SQL statement SELECT *
FROM dbo. Contacts -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE's %'

If no index is available (2130 row (s) affected)
Table 'contacts _ noindex'. Scan count 1, logical reads 568.

If there is an index (2130 row (s) affected)
Table 'contacts _ Index'. Scan count 1, logical read 568.

The index has no effect on I/O.

The index is not used in the query process. In this case, SQL Server feels that using index search requires more work than directly scanning indexes.

Test A non-fully covered query, but more conditions are provided.

We can modify the query to make it the same, but only to reduce the scope of the query results and increase the benefit of using the index. The following is the execution result.

SQL statement SELECT *
FROM dbo. Contacts -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE 'ste %'

If no index is available (107 row (s) affected)
Table 'contacts _ noindex'. Scan count 1, logical reads 568.

If there is an index (107 row (s) affected)
Table 'Contact _ Index'. Scan count 1, logical reads 111.

The index affects I/O from 568 times to 111 times.

Note

SQL Server accesses 107 entries, all of which are within the continuous range of indexes. Each entry tag is used to obtain the corresponding row data. These rows are not consecutive in the table.

These queries use indexes, but they are not as effective as the first overwriting query, especially in IO reading.

You want to read 107 indexes and then get 107 data records, resulting in 107 reads.

In the previous query, 2130 rows of data were requested and no indexes were used. In this request, 107 rows of data are indexed. Do you know the critical point of using indexes? At a later level, we will introduce this aspect.

Test A fully covered Aggregate Query

The last example is an aggregate query that includes count calculation.