Sql-server – Truncate Table With Partitions – aligned/ non-aligned indexes

partitioningsql serversql-server-2016truncate

SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table:

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

An article about the TRUNCATE TABLE WITH PARTITIONS contains the next note:

To start using the new TRUNCATE TABLE statement option, make sure that
all your table indexes are aligned with the source table. If there are
non-aligned indexes in your table, drop or disable these indexes,
truncate the partition(s) you want then rebuild the indexes again.

Does SQL Server 2016 provide an easy way (e.g. a query) to determine if the indexes of the partitioned tables are all aligned? (I want to avoid having to drop or disable indexes.)

Update 1

Example output of link:

*One index consists of 2 columns, but only one column is used in the partition function.

Is the index PK_TEST1 aligned or non-aligned?*

  • 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

Best Answer

I put this query together for you that should show you what you want. You can add additional where clauses to trim it further if you need to.

;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
Related Question