Which Index Should I Create when using multiple OrderBy

Question

I'm using Entity Framework Core and Reading some data with thousands of records. Each records has many columns, of that columns I'm using 3 (a, b and c) and doing:

OrderBy(a).ThenBy(b).ThenBy(c);

And then I'm applying sorting and pagination.

In order to improve speed of that query which kind of index (if any) should I create? Modification of the tables is not as frequent as reading. Should I create one index with the 3 columns? in which order? or one per each column?

Answer 1

IF your query uses a WHERE clause also like below ...

SELECT ColumnA, 
FROM Table
WHERE ColumnA = 0
ORDER BY Column1, Column2, Column3

Then create your index on the following order ColumnA, Column1, Column2, Column3.

If you are using OFFSET FETCH NEXT ROWS ONLY for paging that index still works

SELECT ColumnA, 
FROM Table
WHERE ColumnA = 0
ORDER BY Column1, Column2, Column3
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

If your query only has the ORDER BY clause with no WHERE clause, then create the index in the following order Column1, Column2, Column3.