StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A clustered index will give you performance benefits for queries when localising the I/O. Date is a traditional partitioning strategy as many D/W queries look at movements by date.</p> <p>A rule of thumb for a partitioned table suggests that partitions should be around 10m rows in size.</p> <p>It would be somewhat unusual to see much performance gain from a clustered index on a diverse analytic workload. The query optimiser will use a technique called <a href="http://www.databasejournal.com/features/mssql/article.php/1438821" rel="nofollow noreferrer">'Index Intersection'</a> to select rows without even hitting the fact table. See <a href="https://stackoverflow.com/questions/110032/star-schema-design#111044">Here</a> for a post I did on another question that explains this in more depth with some links. A clustered index may or may not participate in the index intersection, so you may find that it gains you relatively little on a general query workload. </p> <p>You may find circumstances in loading where clustered indexes give you some gain, particularly if you have derived calculations (such as <a href="http://en.wikipedia.org/wiki/Earned_premium" rel="nofollow noreferrer">Earned Premium</a>) that are computed within the ETL process. In this case you may get some benefits. If you have a specific query that you know will be executed all the time it might make sense to use clustered indexes for this. Options #2 and #3 are only going to significantly benefit you if you expect this type of query to be the overwhelming majority of the work done by the application.</p> <p>For a flexible system, a simple date range partition with an index on the ID (and date if the partitions hold a range would probably get you as good a performance as any. You might get some benefit from clustering the index limited circumstances. You might also get some mileage from building a cube over the data and ensuring that the aggregations are set up correctly for this query. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload