When Should You Use DESC in Indexes?

 dans Sites anglophones  étiqueté indexing par

The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order.

Now, for the long answer. When you create indexes, you can either create them in ascending order – which is the default:

CREATE INDEX Reputation ON dbo.Users(Reputation);

Or descending order:

CREATE INDEX Reputation ON dbo.Users(Reputation DESC);

If your query orders the data in descending order, but your index is in ascending order, like this:

CREATE INDEX Reputation ON dbo.Users(Reputation); GO SELECT TOP 100 * FROM dbo.Users ORDER BY Reputation DESC;

SQL Server