Cluster key on Dimension table

Posted on 2011-10-10
Last Modified: 2012-05-12
I have a surrogate key (auto-increment) in the dimension table and there are some other fields (not business key) that will be used for report query. Should I need to create a clustered index to include these keys ? Or the clustered index should only include business key of Fact tables and I should create a separate index to include those columns that will only used for report query and pivot columns ?


Question by:AXISHK
    LVL 25

    Accepted Solution

    If the reports are accessed frequently, then you must include them in the clustered index. It all depends upon your usage. If the usage is not much and only for reports, its better to include them in non-clustered ones. Again, including them in clustered, will create overhead while doing DML statements on the table. So, just test before puttting them on board.

    Author Closing Comment


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now