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
[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
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?
CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1] ( [Id], [PartitionKey] ) ON [PartitionKeyScheme]([PartitionKey])
CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1] ( [PartitionKey], [Id] ) ON [PartitionKeyScheme]([PartitionKey])