Descending Indexes for SQL Server
contexts
Indexing is one of the most important means of optimizing query performance in relational databases. For scenarios where large amounts of data need to be processed, a reasonable indexing strategy can significantly reduce the query time.
Especially in complex queries involving multi-field sorting, choosing the right type of index (e.g., descending index) is especially important. In this article, we will discuss how to use descending indexes to optimize query performance in SQL Server, and demonstrate their application effects through examples.
1、Establish the test environment
Test Environment: SQL Server 2012
The table structure is as follows
USE [test]
GO
CREATE TABLE [dbo].[tt8](
[id] INT IDENTITY(1,1) NOT NULL,
[win_num] [int] NOT NULL DEFAULT ((0)),
[lost_num] [int] NOT NULL DEFAULT ((0)),
[draw_num] [int] NOT NULL DEFAULT ((0)),
[offline_num] [int] NOT NULL DEFAULT ((0)),
[login_key] [nvarchar](50) NULL
CONSTRAINT [PK_user_T] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert test data
--Insert test data
DECLARE @i INT;
DECLARE @sql NVARCHAR(MAX);
SET @i = 1;
WHILE @i <= 9
BEGIN
IF @i % 2 = 0
BEGIN
SET @sql
= N'INSERT INTO [dbo].[tt8]
(
[win_num] ,
[lost_num] ,
[draw_num] ,
[offline_num] ,
[login_key]
)
VALUES (
''' + CAST(@i+2 AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i-1 AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N'''
);';
END;
ELSE
BEGIN
SET @sql
= N'INSERT INTO [dbo].[tt8]
(
[win_num] ,
[lost_num] ,
[draw_num] ,
[offline_num] ,
[login_key]
)
VALUES (
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N''' ,
''' + CAST(@i AS NVARCHAR(3000)) + N'''
);';
END;
EXEC (@sql);
SET @i = @i + 1;
END;
Browse Data
SELECT * FROM [dbo].[tt8]
2, build the query statement
The query statement is as follows, you can see that this is a combination of field sorting, the requirements in accordance with the draw_num value of the positive order, for the same draw_num value, in accordance with the win_num value of reverse order
select top 10 * from [dbo].[tt8] order by [draw_num] asc,[win_num] desc
Build a non-aggregated combined index based on the query statement
CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num] ON [dbo].[tt8]
(
[draw_num] ASC,
[win_num] ASC
)WITH (online= ON) ON [PRIMARY]
GO
After building a non-aggregated index, the execution plan is as follows, you can see that you can't use the non-aggregated index [IX_tt8_draw_numwin_num] that you just built, because when you build the index, the sort order of the two fields is a unidirectional traversal, with a uniform ascending order or a uniform descending order
The following execution plan illustrates that after the database engine scans the aggregated index, it needs to sort the [win_num] field in reverse order, so you will see the sort operator
The query results are as follows, the query results are not problematic
3, build descending index
So, can the index be built with [draw_num] ascending and [win_num] descending in the sort order of the query statement?
The answer is yes, create a new index with [draw_num] ascending and [win_num] descending.
CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num_reverse] ON [dbo].[tt8]
(
[draw_num] ASC,
[win_num] DESC
)WITH (ONLINE= ON) ON [PRIMARY]
GO
After the index is built, the structure of the unaggregated index is roughly like this, with the first field in ascending order and the second field in descending order
Query again, the query result is as follows, no problem
The execution plan is as follows, you can see that this time the index [IX_tt8_draw_numwin_num_reverse] is utilized, and then combined with the aggregated index to return the result
You can see the storage structure of the aggregated index/primary key index, and the winnum field is stored in sequential sorting
SELECT TOP 10 * FROM [dbo].[tt8]
The storage structure of the [IX_tt8_draw_numwin_num_reverse] index is stored with the [win_num] field in reverse order and the [draw_num] field in ascending order
The disadvantage of this inverted index is that when inserting or updating data into the table, you need to sort the [win_num] field in reverse order before inserting or updating it into the [IX_tt8_draw_numwin_num_reverse] index, so there will be a little loss of performance
select [draw_num],[win_num] from [dbo].[tt8] with (INDEX([IX_tt8_draw_numwin_num_reverse]))
Inquiry results
This example shows that for a combined field sort statement, when multiple fields are sorted in an inconsistent order, an index that builds only a single field cannot be utilized, for example, the following index that builds only a [draw_num] field cannot be used with the [IX_tt8_draw_num] index when the following statement is encountered
select TOP 10 * FROM [dbo].[tt8] ORDER BY [draw_num] ASC,[win_num] DESC
Single Field Indexes
CREATE NONCLUSTERED INDEX [IX_tt8_draw_num] ON [dbo].[tt8]
(
[draw_num] ASC
) WITH ( ONLINE = ON ) ON [PRIMARY]
GO
You must create a combination of sorted field indexes, and index field sorting to be consistent with the query statement, this index is called Descending Indexes inside Oracle
summarize
Descending Indexes were supported in earlier versions of SQL Server and Oracle, while MySQL did not introduce this feature until version 8.0, reflecting the evolution of functionality across different database systems.
In performance-critical scenarios, commercial databases tend to offer more powerful and optimized features. However, indexing strategies need to be chosen carefully, especially when faced with queries where fields are not in the same sort order, and the right index can greatly improve query performance.
reference article
/sqlservertip/1337/building-sql-server-indexes-in-ascending-vs-descending-order/
/free-sql-video-troubleshoot-slow-running-query-sql-server-extended-events-wait-stats/
Join our weibo group to discuss database technology and topics related to SQL Server, MySQL, PostgreSQL, and MongoDB with us.
WeChat is for learning and communication purposes only, without any advertising or commercial activities.
If anything is wrong, please feel free to clap o(∩_∩)o
This article is copyrighted and may not be reproduced without the author's permission.