Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Cluster key on Dimension table

Posted on 2011-10-10
Medium Priority
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

TempDBA earned 2000 total points
ID: 36947002
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

ID: 36971999

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

577 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