1

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is unique and the column [PartitionKey] is reused about 20 million times per table, but each unqique value isn't spread over different partitions. [PartitionKey] is a formatted date(yyyyMM) stored as an int.

Table Definition

CREATE TABLE [dbo].[Table1](
    [Id] [uniqueidentifier] NOT NULL,
    [PartitionKey] [int] NOT NULL,
    [CreationDate] [datetime] null,
CONSTRAINT [PK_NC_Table1] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
))

Considering the following queries:

Query 1: Search for unique result (most important)

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.id = @id and t1.PartitionKey = @partitionKey

Query 2: Search for a range of data within a partition

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.PartitionKey = @partitionKey
AND t1.CreationDate > @creationDate

Would the order of a partitioned Clustered index matter? If so, how does it affect performance and what order would be best to suit my case?

Aligned Index: [Id] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [Id],
    [PartitionKey]
) ON [PartitionKeyScheme]([PartitionKey])

Aligned Index: [PartitionKey] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [PartitionKey],
    [Id]
) ON [PartitionKeyScheme]([PartitionKey])
  • Perhaps I'm missing something but I believe the partitioning key must be part of all unique index keys, including the primary key. You'll could add PartitionKey as the second column of the nonclustered primary key and create a non-unique clustered index on CreateDate. Alternatively, a clustered PK and a non-unique non-clustered index on CreateDate would cover the second query and provide good performance of the first too. – Dan Guzman Oct 8 '15 at 11:43
2

You need to think about your partitions as if they are separate tables.

A clustered index on a partitioned table effectively has the partition number as its first key. For you, with a partition per unique PartitionKey, the clustered index will essentially have that column in the front of it. Or as good as...

I'd expect having Id first would feel better, in case you don't know the month and need to search all partitions. It'll seek on each partition rather than scan them.

  • So, if I understand correctly both of my defined clustered indices will result in the same index? [PartitionKey] first,[Id] second. – annemartijn Oct 8 '15 at 11:34
  • Not exactly. The first key will be the partition number. But in your case, the impact of that should be similar to your partitionkey value. – Rob Farley Oct 8 '15 at 11:40
  • So, if I always now the [PartitionKey] value if I know the [Id] value it will be best if I have an index with [PartitionKey] first since the [id] seek will be done on a singular partition. If that's not the case it would be best to have [Id] first. – annemartijn Oct 8 '15 at 12:28
  • 1
    If you have a filter on the partitionkey, it will figure out which partitions to look in and avoid the rest. So you really don't need to include the partitionkey in the index for performance. – Rob Farley Oct 8 '15 at 14:06
  • 1
    Because it's not usually distinct within the partition. – Rob Farley Oct 8 '15 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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