Indexing.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Indexing.

Indexing.

Rate Topic

Display Mode

Topic Options

Author

Message

peacesells

Posted Tuesday, January 08, 2013 6:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:44 AM
Points: 147, Visits: 721

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.

Post #1404511

SQLRNNR

Posted Tuesday, January 08, 2013 10:06 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1404539

Bhuvnesh

Posted Tuesday, January 08, 2013 11:11 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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 BigGrin

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1404550

Jason-299789

Posted Wednesday, January 09, 2013 12:48 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838, Visits: 2,200

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

Post #1404573

« Prev Topic | Next Topic »

Permissions