2

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
Improve this question
11
  • 5
    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. Commented Jun 24, 2020 at 11:10
  • 2
    Wouldn't be better to just add a second index by ID DESC ?. You can try a few queries and check the execution plan. Commented Jun 24, 2020 at 11:12
  • 1
    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). Commented Jun 24, 2020 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. Commented Jun 24, 2020 at 11:21
  • 3
    @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. Commented Jun 24, 2020 at 12:19

5 Answers 5

Reset to default
2
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
2

There is no difference according to the following

enter image description here

Improve this answer

Comments

2

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.

Improve this answer

Comments

1

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

Improve this answer

Comments

1

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:

  1. Have more than order by column
  2. Your index has more than one column
  3. 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?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.