Which index to choose ASC or DESC?

Question

SQL

There is a table to which requests constantly go. Such:
SELECT TOP 50 * FROM table ORDER BY id DESC;
Also, new records are added to the table, with a large id.
Id - column identifier.
What kind of sorting for the index to choose and how important it is.
What would be fast, and also did not have to rebuild or reorganize the indexes.

Answer 1

I don't quite understand what you are trying to achieve.
ASC and DESC are just directions for sorting.
SELECT TOP 50 * FROM table ORDER BY id DESC - will give you the last 50 records
SELECT TOP 50 * FROM table ORDER BY id ASC - will give you the first 50 records.
Explain, maybe I have not yet comprehended Zen?

Answer 2

The sort order of an index only affects when creating indexes with composite keys, i.e., with keys from several fields. That is, if you have an index with the key (field1, field2, field3) and queries with the following sort are more frequent:
ORDER BY field1 ASC, field2 DESC, field3 ASC, then you should use the index (field1 ASC, field2 DESC, field3 ASC) or (field1 DESC, field2 ASC, field3 DESC). When your index has a key from one field, then its sort order does not matter, because SQL engine can scan an index in both directions.
For adding records and for selecting single records, the sort order of the index does not matter - the performance will be absolutely the same, but for selecting a sequence of records, i.e. when scanning the index range, this order can already play, but you need to understand that on sufficiently large index ranges. If you just rake out only the first 50 records and without WHERE, then the difference will be imperceptible.