multiple cluster indices effect

My question is about limitation of clustered index on a table. By theory, in a single table we can have only one cluster index. But 开发者_Go百科what if I have datetime columns in a table say "From date" and "To date"? These columns will often required in WHERE clause to populate reports in my application. And if I also require a cluster index on primary key in the same table, then still how to get advantage of cluster index on other columns? In this case my queries will still run slower with larger records.

---

In practice, you also can have just a single clustered index on a table - since the table's data is physically ordered by that clustered index.

If you need two datetime columns frequently in WHERE clauses, the best choice would be to have a non-clustered index on those two columns, and possibly include additional columns that you frequently retrieve with those queries, in order to make it a covering index.

There's really not much difference between a good, covering non-clustered index, and the clustered index, in terms of query performance.

However, you don't want to bloat your clustered index, since those columns will also be added to all non-clustered indices on the same table - keep it small, preferably an INT, ever-increasing, stable (not changing) and you should be just fine.

---

Another option is indexed (or materialized) views: you could create multiple views on the table, each with a different clustered index. That might be useful in a reporting scenario, but indexed views have lots of restrictions and will affect the performance of queries that modify the table data. Books Online has all the information you need to create and test them.

I suspect your real requirement is indeed to implement a reporting solution, and if so then it might be best to do it properly: create a separate database with a schema optimized for reporting (Google "star schema") and load data regularly from the main database into the reporting one. But that's a whole new area of development to investigate, and I wouldn't rush into it.

---

If you need the performance of a cluster index table for multiple indexes of the same table, the only route I see is holding a copy of the table for each cluster index.

---

The clustered index effects the physical storage of the data in a table, so by definition there can only be one. You can widen your clustered index to include the other columns, but this can have its own disadvantages.

The performance advantage from a clustered index is that the records are stored in a manner that reflects the index (which is why random inserts and updates to the clustered index very quickly fragment the table), and therefore query performance based on this index can be as good as performance as reads from your storage device, you can't get this on other indexes.

---

I suggest that you choose the index from which you would derive the most benefit from clustering and make that your clustered index. Make the rest of the indexes non-clustered. You may want to run some tests to find out what benefits will be derived from making different indexes clustered vs. non-clustered.

Share and enjoy.