SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing.


Indexing.

Author
Message
peacesells
peacesells
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 1492
I know this is a situation based question but on the general though, what columns would be best suitable to have indexes on in dimension and fact tables.I have working to recreate indexes on a Datamart and there are few situation where PK is on the surrogate keys and unique clustered index on the business keys. My approach would be to create a PK drive clustered index on the business key and created nonclustered index on the surrogate keys. Any input would be helpful.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68469 Visits: 18570
Hard to say either way without knowing what the table design(s) is(are). In addition is to know what the queries are.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13742 Visits: 4077
peacesells (1/8/2013)
what columns would be best suitable to have indexes on in dimension and fact tables.
indexs should include column which are part of

Where
order by
group by
JOINs ON clause.
partition by......etc

and column which are invole in SELECT should be part of INCLUDE of covering index. thats the generic answer of your generic question :-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5141 Visits: 3232
Personally I put the Primary Key clustered index on the surrogate Key in the Dimension, with a non-clustered index on the Business key.

Unless the dimension is a Type 1, I would avoid putting a Unique index on the business key as with Type 2 dimensions you will get repeated business key, and therefore need to add an alternative column to maintain the uniqueness.

The main reason is that Surrogate keys are the glue that bind Fact table to dimension table, and so if you want to enforce RI (which I would recomend) between Fact and Dimension then the Surrogate key needs to be the PK.

As for clustering, the reason I suggest the Surrogate key as the clustered index is that you are only ever adding to the end of the Index rather than with a business key which you can be inserting data into the middle of the index thus leading to heavier fragmentation of said index.

That doesnt mean I wouldnt create a Non-clustered index on the business key, which is be the sensible thing to do in order to aid the ETL and Filtering on the reporting side.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search