March 11, 2014 at 2:54 pm
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.
March 12, 2014 at 3:47 am
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')
March 12, 2014 at 9:33 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply