Avatar of AXISHK
AXISHK
 asked on

Index on Database

I have two tables and no index is defined. This is a data warehouse tables that will be loaded into MS AS Cube.

DIM_CURRENCY (CURRENCY_IDX, CURRENCYCODE, NAME, SYMBOL, RATE)

FACT_SALES_INVOICE (CURRENCY_IDX, REGION_COUNTRY, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX, BASEAMOUNT, QTY, INVOICENO, COMPANY)

What kind of index should I create on each tables ?
As there is no primary key on fact table, should I create individual index key for each attribute, or a single key combining all attributes ?

Tks
DatabasesMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Vitor Montalvão

You should have at least a primary key in both tables and a foreign key in the fact tables to guarantee the relationship between tables.
Only after that you should start think in another indexes but that's depends on your queries.
AXISHK

ASKER
Should I create separate logical index for each foreign key, or combine all foreign key within a single logical index ?

Tks
Vitor Montalvão

Normally you should create an index for each FK so will boost the JOIN between tables.
Don't create a composite index just for saving indexes.
Your help has saved me hundreds of hours of internet surfing.
fblack61
AXISHK

ASKER
One quick question, what's my consideration to create a composite index over  a single index for each FK ?  Tks
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question