Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following table.

create table T (K1 date, K2 int, ....) on partitionScheme(K1)

The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). Which of the following primary key (the column order is different) is preferred?

alter table T add primary key clustered (K1, K2)
alter table T add primary key clustered (K2, K1)

Or should the PK be non-clustered and create another clustered index?

share|improve this question

closed as unclear what you're asking by Paul White, Max Vernon, RLF, RolandoMySQLDBA, Marian Oct 30 at 20:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Are you kidding? Isn't the question clear? Kenneth Fisher even gave a good answer. –  u23432534 Oct 31 at 14:47

3 Answers 3

First I'm assuming since you didn't specify that you want your primary key to be clustered. It's the default and most common usage. If not then the order of your column will depend more on the selectivity of the two columns and your usage.

However, if you are making your primary key clustered then if you look in this Technet article on the aligning indexes with partitions it mentions under the Clustered indexes section that if you do not include the partitioning column in the clustered index it will be done for you. Both of your options do include the partitioning column so that isn't an issue but should probably be kept in mind.

All of that being said everything nothing I've read says it makes any difference as long as the clustered index contains the partitioning column (making it aligned). Personally I would probably put the partitioned column first. In my mind that would seem to allow SQL to decide which partition to look in before looking at any other columns in the index.

EDIT: Based on the information you've given there is no real need to have a separate PK and CIX in this case. This of course assumes that the combination of K1, K2 is unique.

share|improve this answer

You do not have to include your partition key in your clustered index if the primary key itself is not partitioned.

You can create an identity column to serve as the primary key and the clustered value, but partition the table by another value.

That would be my preference because it will result in smaller indexes because the cluster key is smaller.

CREATE TABLE [T] (TId int identity, K1 date, K2 int, K3 varchar(10), ...) 
ON PartitionScheme(K1)

ALTER TABLE T ADD PRIMARY KEY CLUSTERED (TId) ON [primary]
share|improve this answer
1  
If the clustered index is unique, as it is with a primary key, you must explicitly specify that the clustered index key contain the partitioning column. –  Robert L Davis Oct 30 at 16:21

if you have identity field, PK should be on 'K1' and 'id_filed'

create table  T (...) 
on partitionscheme(K1)

alter table T add primary key clustred (K1,id_field) on partitionscheme(K1)

if not

alter table T add primary key clustred (K1) on partitionscheme(K1)

,but the clustered index always must be on same file group as table.

share|improve this answer
1  
If the clustered index is unique, as it is with a primary key, you must explicitly specify that the clustered index key contain the partitioning column. –  Robert L Davis Oct 30 at 16:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.