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