Clustered Index

Question

Clustered Index

kk.86manu

Ten Centuries

Points: 1268
More actions
April 16, 2015 at 1:36 pm

#316657

Hi All,
Is it always the best practice to have the partition column also as the column for clustered index?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

What do you mean by "Partition Column"?

Partition get's used in many contexts: Partition key, Partitioned table... A partition key is generally a non-distinct value/key that is used to group a set of values. It could also be a value that you use to indicate on which table partition a would belong. In ether case - a key or value that defines a "partition" is not unique. A clustered index requires a unique key/value. Either way, the answer would be NO - a partition column will contain duplicate values and therefore cannot be used as the column in a clustered index.

I guess you will need to clarify what you mean by partition column to provide you with a better answer.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

kk.86manu (4/16/2015)
Hi All,
Is it always the best practice to have the partition column also as the column for clustered index?

No. It seriously depends. If the clustered index is also a unique index and you're using partitioned tables, the partitioning column will automatically be added to it and any other unique index you may have UNLESS you don't mind having a non-aligned NCI, which kind of defeats the whole purpose of partitioning to begin with and will kill the ability of SWITCH in and out (if that's important to you).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)