Should I explicitly list partitioning column as part of the index key or it's enough to specify it in the ON clause with partition schema?

Question

I have SQL Server 2019 where I want to partition one of my tables. Let's say we have a simple table like so:

IF OBJECT_ID('dbo.t') IS NOT NULL 
    DROP TABLE t;

CREATE TABLE t 
(
    PKID INT NOT NULL, 
    PeriodId INT NOT NULL, 
    ColA VARCHAR(10), 
    ColB INT
);

Let's also say that I have defined partition function and schema. The schema is called [PS_PartitionKey]

Now I can partition this table by building a clustered index in a couple of ways.

Like this:

CREATE CLUSTERED INDEX IX_1 ON t ([PKId] ASC ) 
ON [PS_PartitionKey]([PeriodID])

Or like this:

CREATE CLUSTERED INDEX IX_1 ON t ([PKId] ASC, [PeriodId] ASC ) 
ON [PS_PartitionKey]([PeriodID])

As you can see, in the first case I did not explicitly specify my partitioning column as part of the index key, but in the second case I did. Both of these work, but what's the difference?

A similar question would apply if I were building these as non-clustered indexes. Using the same table as an example. Let's say I start by creating a clustered PK:

ALTER TABLE [dbo].t 
    ADD CONSTRAINT PK_t 
        PRIMARY KEY CLUSTERED ([PKId] ASC, [PeriodId]) ON [PS_PartitionKey]([PeriodID])

Now I want to define additional non-clustered index. Once again, I can do it in two ways:

CREATE NONCLUSTERED INDEX IX_1 ON t ([ColA] ASC) 
ON [PS_PartitionKey]([PeriodID])

or:

CREATE NONCLUSTERED INDEX IX_1 ON t ([ColA] ASC, [PeriodId] ASC) 
ON [PS_PartitionKey]([PeriodID])

What difference would it make?

Should I explicitly list partitioning column as part of the index key or it's enough to specify it in the ON clause with partition schema?

0 Answers0