Partitioned table and data compression

• N_Muller

  SSCarpal Tunnel

  Points: 4037

  More actions

  March 11, 2014 at 2:54 pm

  #286702

  I have a partitioned table with six partitions, with a partition function defined for an integer variable, and values 1-5. I use the partitions function on a table where data and indexes are page-compressed.

  create partition function pf_x (int) as range left for values (1,2,3,4,5)

  go

  create partition scheme ps_x as partition pf_x all to [PRIMARY]

  go

  create table Tbl

  (

  IndexID int identity(1,1),

  PartitionID int,

  MyColumn varchar(100),

  constraint PK_Tbl primary key clustered ( PartitionID, IndexID )

  with (page_compression = page) on ps_x ( PartitionID )

  ) on ps_x ( PartitionID )

  go

  At one point I split the last partition:

  alter partition scheme ps_x next used [PRIMARY]

  go

  alter partition function pf_x() split range (6)

  go

  It appears to me that the new partition of the table is not compressed.

  Am I missing something? Any help is appreciated.

• MysteryJimbo

  SSC-Insane

  Points: 24203

  More actions

  March 12, 2014 at 3:47 am

  #1696274

  Splitting a partition will not change the compression level.

  Easy enough to check. Look at the data_compression_desc column for each partition and index_id.

  SELECT *

  FROM sys.partitions AS p

  WHERE object_id = OBJECT_ID('dbo.tablename')

• N_Muller

  SSCarpal Tunnel

  Points: 4037

  More actions

  March 12, 2014 at 9:33 am

  #1696458

  Thanks. My confusion was due to the fact that when I first create the table and I script it, it returned "data_compression = page" only. But once I added new partitions, the scripting of the table returned "data_compression = page for partition...", which I thought SQL server was only compressing the original partitions, but not the new ones.