MS SQL: Performance for querying ID descending

Question

This question relates to a table in Microsoft SQL Server which is usually queried with ORDER BY Id DESC.

Would there be a performance benefit from setting the primary key to PRIMARY KEY CLUSTERED (Id DESC)? Or would there be a need for an index? Or is it as fast as it gets without any of it?

Table:

CREATE TABLE [dbo].[Items] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Category] INT NOT NULL,
    [Name] NVARCHAR(255) NULL,
    CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Query:

SELECT TOP 1 * FROM [dbo].[Items]
WHERE Catgory = 123
ORDER BY [Id] DESC

A primary key should be sufficient. Making the primary key descending -- for an increasing primary key -- would wreak havoc on inserts. Well, not exactly, but it would require a lot of data movement for every insert. — Gordon Linoff, Jun 24 '20 at 11:10
Wouldn't be better to just add a second index by ID DESC ?. You can try a few queries and check the execution plan. — Marc Guillot, Jun 24 '20 at 11:12
Why do you have `ORDER BY` in a production query? There aren't as many reasons to use `ORDER BY` as you may think! (And the ordering of an index or PK has very little effect on `ORDER BY` because `ORDER BY` is evaluated after the data has already been read into the server's data buffer: the ordering generally happens all in-memory). — Dai, Jun 24 '20 at 11:19
"Or is it as fast as it gets without any of it?" - we need to see your full query **and** `CREATE TABLE` definition (including all associated indexes) and actual execution-plan. Anything else is conjecture and speculation. — Dai, Jun 24 '20 at 11:21
Your `SELECT TOP 1` query doesn't list any columns to return. — Dai, Jun 24 '20 at 11:49
If most of your queries on this table will filter on the category, then I would add a nonclustered index on this column. — Wouter, Jun 24 '20 at 12:15
@Dai There's a very good reason though: predictable ordering. Also, if the query analyzer sees that the ordering columns are in an index it can simply use the index as a basis to select the rows and forego with sorting altogether. — TT., Jun 24 '20 at 12:19

score 2 · Accepted Answer · answered Jul 02 '20 at 20:49

Would there be a performance benefit from setting the primary key to PRIMARY KEY CLUSTERED (Id DESC)?

Given as you show is: IT DEPENDS.

The filter is on Category = 123. To find all entries of Category 123, because there is NO INDEX defined, the server has to do a table scan. Unless you havea VERY large result set, and / or some awfully comically bad configured tempdb and very low memory (because disc is only used when running out of memory for tempdb) the sorting of hte result will be irrelevant compared to the table scan.

You are literally following the wrong tail. You are WAY more likely to speed up the query by adding a non-unique index to Cateogory so that the query can prefilter the data fast based on your query condition.

If you would analzy the query plan for this query (which you should - technically we should not even ANSWER this quesstion without you showing SOME effort, and a look at the query plan is like the FIRST thing you do) you would very likely see that the time is spent on on the query, NOT the result sort.

Sometimes with clustered tables, one needs to analyze overall use and cluster on something else than a mindlessly ascending primary key, which can be supported by a basic index, and is usually supported by some sort of sequence generator for uniqueness. If Category is very structural, it might be a good left element of a clustered index, even alone, not unique. Insertion growth would occur at the end of categories, but if not too many categories, that is not a big deal! Some tables have few deletions, so holes might not be a big deal, either! Then the data for one category is co-located. — David G. Pickett, Jul 02 '20 at 21:09

Gabriele Franco · Answer 2 · 2020-07-08T07:09:12.890

Creating an index in asc or desc order does not make a big difference in “ORDER BY” when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.

Look this article that do many example:

https://www.mssqltips.com/sqlservertip/1337/building-sql-server-indexes-in-ascending-vs-descending-order/

In your scenario I advise you to create an index on Category Column without include “Id” because the clustered index is always included in non-clustered index.

score 1 · Answer 3 · answered Jul 03 '20 at 08:23

1

There is no difference according to the following

answered Jul 03 '20 at 08:23

Asela Sampath

364
2
6

score 1 · Answer 4 · answered Jul 06 '20 at 02:15

1

I'd suggest defining an index on (category, id desc). It will give you best performance for your query.

answered Jul 06 '20 at 02:15

ch_g

1,394
8
12

score 1 · Answer 5 · answered Jul 06 '20 at 06:09

As others have indicated, an index on Category (assuming you don't have one) is the biggest performance boost possible here.

But as for your actual question. For a single order by query like you have, it does not matter if the query/index is ordered by desc or asc as far as performance goes. SQL Server can swap those easily (starting a the beginning or the end of the data structure)

Where performance becomes an issue for performance is when you:

Have more than order by column
Your index has more than one column
Your order by is opposing the order on the index.

So, say your Primary Key had ID asc and Category asc, and then you query by ID asc and Category desc. Then SQL Server can't use the order on the index to do the search.

There are a few caveats and gotchas. After searching a bit, this answer seems to have them listed:

SQL Server indexes - ascending or descending, what difference does it make?

MS SQL: Performance for querying ID descending

5 Answers5