SQL Server 2016 引入了對 TRUNCATE TABLE 語句的修改，它允許您從表中截斷特定分區或一組分區：

TRUNCATE TABLE < tablename > WITH (PARTITIONS (< partition_id >));

一篇關於 TRUNCATE TABLE WITH PARTITIONS 的文章包含以下註釋：

要開始使用新的 TRUNCATE TABLE 語句選項，請確保所有表索引都與源表對齊。如果您的表中有未對齊的索引，請刪除或禁用這些索引，截斷您想要的分區，然後再次重建索引。

SQL Server 2016 是否提供了一種簡單的方法（例如查詢）來確定分區表的索引是否全部對齊？（我想避免不得不刪除或禁用索引。）

---

更新 1

連結的範例輸出：

*一個索引由2列組成，但分區函式中只使用了1列。

索引 PK_TEST1 是對齊的還是非對齊的？*

• 索引名稱：PK_TEST1
• partition_function：psTEST1
• 分區序數：1
• key_ordinal: 1
• is_included_column: 0
• 列名：COLUMN1

• 索引名稱：PK_TEST1
• partition_function：psTEST1
• 分區序數：0
• key_ordinal: 2
• is_included_column: 0
• 列名：COLUMN2

我為您整理了這個查詢，它應該向您展示您想要的內容。如果需要，您可以添加額外的 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

引用自：https://dba.stackexchange.com/questions/240911