1

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)
ca9163d9
  • 27,283
  • 64
  • 210
  • 413

1 Answers1

0

Have a look at How important is the order of columns in indexes? and this answer to Where should the partitioning column go in the primary key on SQL Server?:

You should go from the most selective field to least selective, regardless of whether a particular field is the partitioning field - as long as the partitioning field is in the index, so as to keep the index aligned with the partition.

Community
  • 1
  • 1
Monty Wild
  • 3,981
  • 1
  • 21
  • 36
  • Should clustered primary key have more consideration than index? – ca9163d9 Oct 14 '13 at 15:27
  • You should almost always set the best PK you can (the least amount of data that uniquely identifies the row with the fields in order from most selective to least selective), and *if you need to* (Display Execution Plan in SSMS will often tell you), you may also need to add other indexes, so a PK should have *first* consideration, but not necessarily *more* consideration. – Monty Wild Oct 14 '13 at 21:31