Indexing fact tables

Recently I was working on datawarehouse project during which i had to automate generation of indexes for fact tables. Tuning SQL Server relational warehouse is a complex task. As you may already know fact tables have lots of foreign keys into dimension tables in star schema in relational warehouse.  These foreign keys should be your first candidates for indexes as most of the queries in a relational warehouse are joins on a surrogate keys of fact tables with dimensional tables.

Script below will help you to automate generation of indexes on fact tables from foreign keys. Just change <fact table name> to an actual fact table name.

Hope it saves you some time in tuning your datawarehouse 

select 'if exists (select name from sys.indexes where name=''IX_<fact table name>_'+c.name+

''') drop index <fact table name>.IX_<fact table name>_'

+c.name

from sys.foreign_key_columns fkc

inner

join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id

inner

join sys.columns c on c.column_id=fkc.parent_column_id

where

fk.parent_object_id=object_id('<fact table name>')

and

c.object_id=object_id('<fact table name>)

select

'create nonclustered index IX_<fact table name>_'+c.name+ ' on <fact table name>('+c.name+')' from sys.foreign_key_columns fkc

inner

join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id

inner

join sys.columns c on c.column_id=fkc.parent_column_id

where

fk.parent_object_id=object_id('<fact table name>')

and

c.object_id=object_id('<fact table name>'')

go

Enjoy it and until next time...