All things being equal, which is a better indexing strategy?
Fact table, 100 million rows of monthly data. Approx. 1.5 million new rows per month. Non partitioned. Users query most recent month, 3 months or 12 months the most, then additional filters after that. Table contains five years of
history (60 months). Reading data is more important to users that data inserts, which happen only monthly, and then just overnight.
Index strategy A:
Unique Clustered index on an identity key - Primary key constraint; non clustered indexes on other filtering columns, including the Date column
Index strategy B:
Non unique Clustered index on date column; primary key constraint on identity column; non clustered indexes on other filtering columns, first excluding date column, then trying with non clustered index on date column
Now, according to what I've "read", scenario B would be better performing for reads, whereas scenario A would be better performing for Inserts. But when I set this up in a test environment, I am seeing the opposite effect in most cases.
When set up as scenario A, a select query filtering on date (where date >= 201004) it does an index scan and the IO stats and query times look good.
When set up as scenario B, with no non-clus
View Complete Post