Could you take a look at my approach to the Data Warehouse indexing (SQL Server 2014 SP1 Ent) below and tell me is it correct or should I change it ? (our Data Warehouse most of the time is used for SSAS Cube and sometimes for direct reports)
My indexing approach:
- All primary key's (in a Dimension tables) have a clustered Index
- For bigger Dimension tables (50k+ of rows) I have a dedicated non-clustered index per frequently use column
- All Fact tables have a PK clustered index implemented (on single non FK column like FactTableKey) – but I red this shouldn’t be placed there
- All foreign key’s (in a Fact tables) have a dedicated non-clustered index (per one FK column in it)
- In addition I’m considering the implementation of a CLUSTERED COLUMNSTORE INDEX on a Fact tables instead of many non-clustered indexes for every FK’s - but in this case I’ve red that this is a not recommended solution
In addition – on my test lab with Visual Studio SSDT – I tried to put a CCI index on a Fact table which belongs to a FILEGROUP but I get an error with information that I cannot use CCI on a table which belongs to a FILEGROUP. What’s strange I can create such CCI from SSMS directly. Visual Stusio issue or SSMS bug ?