I really don't see any way to have multiple clustered indices without storing multiple copies of the data, as it's not clustered unless it's physically stored in that order.
In DB2, the data is stored once, and physically clustered in multiple dimensions. All it needs is a bit of lateral thinking. Consider the following...
You have a collection of objects you want to cluster by shape, by size, and by colour.
Store all the small round red things in one database extent "a".
Store all the small square blue things in extent "b".
Store all the large triangular blue things in extent "c".
Create a new type of index that only knows about extents. Call it a Multiple Dimension Clustering type of index.
Define an index for size. This has 3 entries: large, "c"; small, "a"; small, "b".
Define an index for shape. This has 3 entries: round,"a"; square,"b"; triangular,"c"
Define an index for colour. This has 3 entries: blue, "b"; blue, "c"; red,"a"
You want all the blue things, the database gets you extents "b" and "c".
You want all the small things, the database gets you extents "a" and "b"
You want the blue square things, the database gets you extent "b"
In each extent that is returned, ALL the rows match your WHERE clause.
So storing a data item once only and physically clustering it in multiple dimensions can be done. You just dedicate a whole extent to store the intersection of all your clustering indexes.
In DB2, you can set the extent size for each filegroup (called tablespace in DB2) so you can tune this value to minimise unused space in the extent. The DB2 MDC index pointers are the same size as normal RID pointers, but only the extent level information is populated, allowing normal and MDC indexes to be used in the same query with standard index AND and OR logic. This is a cool feature in DB2 but with weaknesses as well as strengths. It would be nice if SQL Server also had this technology - I am sure IBM would licence another one of its database patents to Microsoft for a suitable fee.
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016
: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara