sys.partitions

• Michael Brisset

  Valued Member

  Points: 50

  More actions

  January 5, 2009 at 3:51 pm

  #203620

  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!