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, K3 varchar(10), C1 int, C2....)

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 is preferred?

alter table T add primary key (K1, K2, K3)
alter table T add primary key (K2, K3, K1)
share|improve this question

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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