SQLTeam.com | Weblogs | Forums

Indexing on Composite Columns and sorting

sql2005

#1

Good morning.
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,
COLA VARCHAR(13),
COLB VARCHAR(13),
Descript VARCHAR(50),
Quantity INT,
DateEntered DATETIME,
lastDATE DATETIME)
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

COLA,DateEntered
COLB,DateEntered
Descript,DateEntered

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?)
1-
CREATE INDEX IDX01 ON SAMPLE (COLA,DateEntered ASC) INCLUDE (COLB, Descript, Quantity,lastDATE)
2-
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
1-
CREATE INDEX IDX03 ON SAMPLE (COLA,DateEntered,lastDATE ASC) INCLUDE (COLB, Descript, Quantity)
2-
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.


#2

You should add a customer code to the table so you can first cluster on the customer code. (Don't "always" cluster on identity, that's a myth.)

After that, it depends. If you use dynamic SQL, and/or it's otherwise easy to customize queries per customer, and you're willing to duplicate data to improve performance, you can add new clus key columns that duplicate existing columns.

If not, I'd say just let SQL sort the data each time rather than trying to create indexes to match each sort.