Sql-server – Aligned index column order

indexpartitioningsql serversql server 2014t-sql

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])

Best Answer

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.

Related Question