Contents
What is the need of a partition key?
The purpose of a partition key is to identify the partition or node in the cluster that stores that row. When data is read or written from the cluster, a function called Partitioner is used to compute the hash value of the partition key. This hash value is used to determine the node/partition which contains that row.
What is a partition key?
Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key consists of one or more columns that determine the partition where each row is stored.
Where does primary key go in partitioning table?
By default indexes are created on the same Data Space (ie Filegroup or Partition Scheme) as the table. So you have to explicitly place the primary key index on a filegroup. Eg BTW this will create Heap table, which is probably the worst option.
Is the partition key part of the index?
If the index supports a query in such a fashion as to do an index seek (and a possible range scan for multi-row queries), it won’t do a table scan any more than it would in a monolithic table. And, IIRC, the partition scans are only possible if you include the partitioning column in the criteria of the query.
How is the partition key used in SQL?
The partition key is used to determine which partition to put the row in, but I don’t think an index is maintained. There may be stats in the back end on it though. In addition to JNK’s answer, you probably should read this article which discusses aligning table partitions and index partitions.
Where do you put the primary key in SQL?
You are trying to create the non-clustered primary key index on the same partition scheme as the table, but its key columns don’t contain the partition key. By default indexes are created on the same Data Space (ie Filegroup or Partition Scheme) as the table. So you have to explicitly place the primary key index on a filegroup.