0

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
  • The post - Partitioned Tables with Non-Aligned Indexes - has a query that may provide the information you are looking for. – Scott Hodgin Jun 19 at 12:06
  • @ScottHodgin Thank you for the link, but which column or value can I use to differentiate aligned and non-aligned indxes? I've also checked the link as well, but it's still unclear for me. – Odrai Jun 19 at 12:08
  • @ScottHodgin Please have a look at 'Update 1' in the OP. – Odrai Jun 19 at 12:14
  • If the index includes the partition column as one of the key columns and is defined as using the partition scheme, then it is aligned. – Jonathan Fite Jun 19 at 12:25
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.