I am using SQL SERVER 2005 and one of my table have 8 million records. Let say structure of table is as follows
CREATE TABLE SAMPLE(
SAMPLE_ID INT IDENTITY(1,1) PRIMARY KEY,
I have given option to customer to perform sorting assending or decending on any column.
My where clause could be any of following column combinations
With any of these combination customer can sort ASC or DESC base on any column.
This table is very static (only get Insertion, updated and deletion once or twice a day).
Lets say customer enter criteria where he/she is looking for ColA="AAA" and DateEntered BETWEEN "07/01/2016" AND "07/04/2016" and need all records in DateEntered DESC.
Do i have to create 2 indexes to cover this issue (in case if he decide to view DateEntered in Asc order?)
CREATE INDEX IDX01 ON SAMPLE (COLA,DateEntered ASC) INCLUDE (COLB, Descript, Quantity,lastDATE)
CREATE INDEX IDX02 ON SAMPLE (COLA,DateEntered DESC) INCLUDE (COLB, Descript, Quantity,lastDATE)
Now lets say he want to sort with lastDate DESC do i have to create another 2 more indexes
CREATE INDEX IDX03 ON SAMPLE (COLA,DateEntered,lastDATE ASC) INCLUDE (COLB, Descript, Quantity)
CREATE INDEX IDX04 ON SAMPLE (COLA,DateEntered,,lastDATE DESC) INCLUDE (COLB, Descript, Quantity)
My where clause can have only 3 possible combination as shown above but order by could be any columns from table structure. I cannot tell customer that they cannot sort base on a specific column as different customer have different needs.
Would appreciate any input on my problem above.