Problem Defined :
Creating Multiple Clustered Index on Sql Server Table

Why it is needed ?

Our one table is very big (contains around 30 million records currently) and having more than 80 columns (of course not normalized since normalization will force joining for most of the reports which does cut performance to a great extent). We have multiple reports to come from the same table. Some time the report is as short as 10 records.

When we query for some 10 records in this big table, we face performance issue. The query executes in 2-10 minutes.

Initial Solution
So first we created one clustered index on this table which contains two columns. And come out with very fast performing queries which now take 0-1 seconds instead of 2-10 minutes for the same set of records.

But our joy was only for short span. Since we had 15 more reports having different key columns. Multiple indices are possible only through non clustered index(logically ordering of records). We can normally create only one clustered index ( physical ordering of records). The clustered indices used to be much more faster than non clustered once due to obvious reasons. And we need clustered one do get maximum performance.

Sql Server does not allow us to create multiple clustered indices.

Final Solution
We thought and found the way out of this issue as following. And it really worked.

First we created multiple views on the table as was required by our various reports.
Then we added Unique Clustered Indices to these views based on the key fields of our reports. And finally we wrote queries on the table (not on view). The indices we have created on views are actually associated with the table and since they are clustered they give same performance as a clustered index on the table.

Source: http://logicroom.blogspot.com/  (Only archived here, so I don't lose it)