I have 2 columns in a table namely ColA and ColB.all DML operations are through views n every view has
Where clause i.e where ColA=”” with check option .
All most all my DML queries are using where clause on ColB
Where ColB=””
Now my question is I have a clusted index on both ColA and ColB.in which order I have to create cluster index .
i.e ColA ASC,ColB ASC or ColB ASC,ColA ASC.
Is there any performance gain we can achieve with their order
The only way to find out for sure is to try it both ways. It also depends on which queries or SP's are run most frequently.
Generally speaking, you want the most selective column to be first in the clustered index.
I would turn on SET STATISTICS IO ON, and turn on your Graphical Execution plan, and then run some of your most frequently executed queries and see which version of the clustered index gives you the best results.
