sys.partitions

  • sys.partitions is returning two records for most of the partitions in my largest table. I expected only one record for each partition. Is this expected behavior in some cases?

    SELECT * FROM sys.partitions i WHERE i.object_id = object_id('dbo.MyTable') AND i.index_id = 1 AND partition_number=60

    partition_id object_id index_id partition_number hobt_id rows

    -------------------- ----------- ----------- ---------------- -------------------- ------

    72057594535739392 306100131 1 60 72057594535739392 10487012

    72057594560118784 306100131 1 60 72057594560118784 10487012

    If I switch ownership of this partition to another table it appears only some of the data is moved:

    ALTER TABLE MyTable SWITCH PARTITION 60 TO . . .

    partition_id object_id index_id partition_number hobt_id rows

    -------------------- ----------- ----------- ---------------- -------------------- ----------------

    72057594560118784 306100131 1 60 72057594560118784 10487012

    72057594579058688 306100131 1 60 72057594579058688 0

    SQL Server 2005 SP2 CU 9.

    Enterprise (64-bit)

    Can anyone provide some insight into what SQL is doing here?

    Thank you!

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply