Sample Header Ad - 728x90

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?

7 votes
4 answers
764 views
[The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16#aligned-index) says the following > ### 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)
Asked by J. Mini (1269 rep)
May 18, 2025, 05:33 PM
Last activity: Sep 8, 2025, 09:43 AM