Entity Framework 7 supports creation of descending indexes.
An example of what the syntax looks like is below…
Sort order doesn’t really matter much for single column indexes but it can make a big difference for multi-columns indexes.
Below are two examples I’ve ‘borrowed’ from the excellent Building SQL Server Indexes in Ascending vs Descending Order (mssqltips.com) article by Greg Robidoux.
For the following query which orders two columns by different directions…
When our index has both columns ASC like so…
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] ASC )
The query has a cost of 0.102122 👇🏻 … which includes an expensive sort operation highlighted in red.
While on the other hand if we can sort the SubTotal column in the index by descending order to exactly match the query like so…
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] DESC )
We can see the cost significantly reduces to 0.0033123 👇🏻 … this is because the sort operation is no longer needed.
