具有分区对齐/非对齐索引的截断表

Question

Server 2016引入了对TRUNCATE语句的修改，该语句允许您从表中截断特定分区或分区集：

截断表< tablename >和(分区(< partition_id >))；

关于带分区的截断表的文章包含下一个注意事项：

要开始使用新的TRUNCATE语句选项，请确保所有表索引都与源表对齐。如果表中有非对齐索引，请删除或禁用这些索引，截断所需的分区(S)，然后再次重建索引。

Server 2016是否提供了一种简单的方法(例如查询)来确定分区表的索引是否都对齐？(我希望避免不得不删除或禁用索引。)

更新1

链接的示例输出：

*一个索引由2列组成，但分区函数中只使用一列。

索引是对齐的还是非对齐的？*

• index_name: PK_TEST1
• partition_function: psTEST1
• partition_ordinal: 1
• key_ordinal: 1
• is_included_column: 0
• columnName: COLUMN1

• index_name: PK_TEST1
• partition_function: psTEST1
• partition_ordinal: 0
• key_ordinal: 2
• is_included_column: 0
• columnName: COLUMN2

Answer 1

我为您将这个查询放在一起，应该向您展示您想要的内容。您可以添加额外的where子句来进一步修剪它，如果您需要的话。

;WITH CTE_PartCount AS
    (
    SELECT P.object_id
        , P.index_id
        , COUNT(P.partition_number) AS PartitionCount
    FROM sys.partitions AS P
    GROUP BY P.object_id
        , P.index_id
    )
, CTE_Objects AS
    (
    SELECT O.object_id
        , O.name AS ObjectName
        , S.name AS SchemaName
        , I.index_id
        , I.name AS IndexName
        , I.type_desc AS IndexType
    FROM sys.objects AS O
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    WHERE O.is_ms_shipped = 0
        AND O.type_desc = 'USER_TABLE'
    )
, CTE_Summary AS
    (
    SELECT O.object_id
        , O.ObjectName
        , O.SchemaName
        , O.IndexName
        , O.IndexType
        , PC.PartitionCount
        , TablePartCount = FIRST_VALUE(PC.PartitionCount) OVER (PARTITION BY O.object_id ORDER BY O.object_id, O.index_id)
    FROM CTE_Objects AS O
        LEFT OUTER JOIN CTE_PartCount AS PC ON PC.object_id = O.object_id AND PC.index_id = O.index_id
    )
SELECT S.object_id
    , S.SchemaName
    , S.ObjectName
    , S.IndexName
    , S.IndexType
    , S.PartitionCount
    , IsPartitioned = CASE WHEN S.TablePartCount <> 1 THEN 'YES' ELSE 'NO' END
    , IsAligned = CASE WHEN S.TablePartCount = S.PartitionCount THEN 'ALIGNED' ELSE '' END
FROM CTE_Summary AS S