If all of the indexes of a table are partitioned with the same function and scheme but on different columns, is it still considered aligned?

Question

Aligned index

An index that is built on the same partition scheme as its corresponding table. When a table and its indexes are in alignment, the database engine can switch partitions in or out of the table quickly and efficiently while maintaining the partition structure of both the table and its indexes. An index doesn't have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that:

The arguments of the partition functions have the same data type.

They define the same number of partitions.

They define the same boundary values for partitions.

To my shock, I cannot see anywhere in this definition that says that the index is not considered aligned if it is partitioned on a completely different column to the table.

So, is the index below considered aligned with the table?

CREATE PARTITION FUNCTION ByYear (DATETIME2(0))                                        
AS RANGE RIGHT
FOR VALUES ('20000101', '20010101', '20020101',  '20030101');
GO
CREATE PARTITION SCHEME AllToPrimary
AS PARTITION ByYear
ALL TO ([Primary]);
GO
CREATE TABLE Partitioned
(
    Junk NVARCHAR(MAX),
    TablePartitioningColumn DATETIME2(0),
    IndexPartitioningColumn DATETIME2(0)
) ON AllToPrimary(TablePartitioningColumn)
GO
CREATE NONCLUSTERED INDEX PartitionedDifferent
ON Partitioned (IndexPartitioningColumn)
ON AllToPrimary(IndexPartitioningColumn)

Paul White · Accepted Answer · 2025-05-20T09:07:29.390

No, it's not aligned.

Your quote is just the portion that seeks to clarify the potential equivalence of different partition functions.

The following sections speak only of a common partitioning column.

Partitioning clustered indexes

When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index and the partitioning column isn't explicitly specified in the clustering key, the database engine adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column. For more information on clustered indexes and index architecture, see Clustered Index Design Guidelines.

Partitioning nonclustered indexes

When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, the database engine adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. The database engine doesn't add the partitioning column to the index if it's already present in the index. For more information on nonclustered indexes and index architecture, see Nonclustered Index Design Guidelines.

An attempt to SWITCH out a partition of your table will fail with the error:

Msg 4912, Level 16, State 1
'ALTER TABLE SWITCH' statement failed.
The columns set used to partition the table 'dbo.Partitioned' is different from the column set used to partition index 'PartitionedDifferent'.

The documentation could be more explicit about this.

"Aligned" is a convenient shorthand for the idea that each partition (rowset) forming the indexes (and base table) should contain the same rows.

In other words, partition n of the base table should be guaranteed to contain the same rows as partition n of all secondary indexes. This is what allows things like SWITCH to work correctly as a metadata-only operation without physical data movement.

Older versions of the documentation were clearer about this (emphasis added):

Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table. As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table.

and in General Requirements for Switching Partitions:

Partitions must be on the same column. If you are switching a partition from one partitioned table to another, both tables must be partitioned on the same column.

Older documentation also stated the requirements when indexed views are present. This information has simply been lost in modern versions due to consolidation.

An indexed view is partition-aligned with the table it references if the following conditions are true:

The partition functions of the indexes of the indexed view and table:

Define the same number of partitions.

Define the same boundary values for partitions.

The arguments of the partition functions must be the same column.

Selective truncate bug

As a side note, Dan Guzman noticed that the new TRUNCATE TABLE syntax allowing only certain partitions to be truncated has a bug with non-aligned partitions, which will corrupt the structures involved (unless all partitions are specified).

The command should only be allowed with aligned indexes. Unfortunately, the documentation suggests an incomplete test is used:

To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).

As discussed, being partitioned using an equivalent partition function is not sufficient.

Demo

INSERT dbo.Partitioned 
    (TablePartitioningColumn, IndexPartitioningColumn)
VALUES 
    ('1999-07-01', '2000-07-01'),
    ('2000-07-01', '2001-07-01'), 
    ('2001-07-01', '2002-07-01');
-- Check partitions
SELECT *, 
    PF1 = $PARTITION.ByYear(TablePartitioningColumn), 
    PF2 = $PARTITION.ByYear(IndexPartitioningColumn) 
FROM dbo.Partitioned AS P;

Junk	TablePartitioningColumn	IndexPartitioningColumn	PF1	PF2
NULL	1999-07-01 00:00:00	2000-07-01 00:00:00	1	2
NULL	2000-07-01 00:00:00	2001-07-01 00:00:00	2	3
NULL	2001-07-01 00:00:00	2002-07-01 00:00:00	3	4

TRUNCATE TABLE dbo.Partitioned WITH (PARTITIONS (1));
DBCC CHECKTABLE (Partitioned);

Msg 8952, Level 16, State 1
Table error: table 'Partitioned' (ID 260248032). 
Index row in index 'PartitionedDifferent' (ID 2) 
does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 46
Index row (1:341560:0) with values (
IndexPartitioningColumn = '2000-07-01 00:00:00' and
TablePartitioningColumn = '1999-07-01 00:00:00' and 
HEAP RID = (1:333464:0)) pointing to the data row 
identified by (HEAP RID = (1:333464:0)).

DBCC results for 'Partitioned'.
There are 2 rows in 2 pages for object "Partitioned".

CHECKTABLE found 0 allocation errors and 1 consistency errors 
in table 'Partitioned' (object ID 260248032).

repair_rebuild is the minimum repair level for the errors 
found by DBCC CHECKTABLE (dbo.Partitioned).

DBCC execution completed.

Feedback site bug report

score 3 · Answer 2 · answered May 18 '25 at 18:54

So What? You Can't SWITCH

As the quoted piece of documentation says

When a table and its indexes are in alignment, the database engine can switch partitions in or out of the table quickly and efficiently while maintaining the partition structure of both the table and its indexes.

So the real test of alignment is if SWITCH works. Let's try it.

CREATE PARTITION FUNCTION ByYear (DATETIME2(0))                                        
AS RANGE RIGHT
FOR VALUES ('20000101', '20010101', '20020101',  '20030101');
GO
CREATE PARTITION SCHEME AllToPrimary
AS PARTITION ByYear
ALL TO ([Primary]);
GO
CREATE TABLE Partitioned
(
    Junk NVARCHAR(MAX),
    TablePartitioningColumn DATETIME2(0),
    IndexPartitioningColumn DATETIME2(0)
) ON AllToPrimary(TablePartitioningColumn)
GO
CREATE NONCLUSTERED INDEX PartitionedDifferent
ON Partitioned (IndexPartitioningColumn)
ON AllToPrimary(IndexPartitioningColumn)
CREATE TABLE Staging
(
    Junk NVARCHAR(MAX),
    TablePartitioningColumn DATETIME2(0),
    IndexPartitioningColumn DATETIME2(0)
)
GO
ALTER TABLE Partitioned SWITCH PARTITION 2 TO Staging

Even though there is no data in this table, you get the error message that Paul White's answer points out

'ALTER TABLE SWITCH' statement failed. The columns set used to partition the table '[YourDbNameHere].dbo.Partitioned' is different from the column set used to partition index 'PartitionedDifferent'.

This is error number 4912. If you read the error numbers near that one, you will find many other interesting way to mess up a partitioned table.

score 1 · Answer 3 · answered May 19 '25 at 06:12

No, they aren't aligned.

Your quoted text doesn't explicitly mention columns, but it is implicit in this point:

They define the same boundary values for partitions.

If the partitions are on different colums, the boundary values may end up being equal, but they will not be the same values.

score -2 · Answer 4 · edited May 26 '25 at 07:41

No, the indexes would not be considered aligned in that case. For indexes (or partitions) to be aligned, they must be partitioned using the same partition function, the same partition scheme, and on the same column. While using the same function and scheme is necessary, it's not sufficient—alignment also requires that the data is partitioned on the same key.

This is because alignment ensures that the boundaries of each partition line up exactly across the indexes or tables. If you're partitioning on different columns—even with the same function/scheme—the data distribution across partitions will differ, breaking that alignment. Example:

Let’s say you have two indexes:

IndexA is partitioned on column CreatedDate
IndexB is partitioned on column RegionID Even if both use the same range function (say, by year or by integer range) and the same partition scheme, the rows will fall into different partitions depending on their respective values in CreatedDate vs RegionID. So, they aren’t aligned.

This matters especially in SQL Server for things like partition switching, which requires perfect alignment—same function, scheme, and partitioning column.

If all of the indexes of a table are partitioned with the same function and scheme but on different columns, is it still considered aligned?

Aligned index

4 Answers4

Partitioning clustered indexes

Partitioning nonclustered indexes

Selective truncate bug

Demo

So What? You Can't SWITCH