测试:
-- DROP TABLE [DemoDB].[dbo].[Address]
SELECT * INTO [DemoDB].[dbo].[Address] FROM [AdventureWorks2014].[Person].[Address]
GO
USE [DemoDB]
GO
--创建分区函数
CREATE PARTITION FUNCTION [PF_ID](INT)
AS RANGE LEFT FOR VALUES (10000,15000)
GO
--创建分区方案
CREATE PARTITION SCHEME [PS_ID]
AS PARTITION [PF_ID] TO ([PRIMARY], [FG], [FG1] )
GO
--表分区
ALTER TABLE [dbo].[Address]
ADD CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([AddressID] ASC)
ON [PS_ID]([AddressID])
GO
--删除1/4的数据,使其产生碎片
DELETE FROM [dbo].[Address] WHERE [AddressID]%4=0
--查看表的分区
SELECT * FROM sys.partitions WHERE [object_id]=OBJECT_ID('Address')
SELECT index_id,partition_number,avg_fragment_size_in_pages,page_count,alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(),Object_id('Address'),NULL,NULL,NULL)
--现在重建表第三个分区的索引,再看看,碎片减少了。
ALTER INDEX [PK_Address] ON [dbo].[Address]
REBUILD PARTITION = 3
GO
--重建表所有分区的索引,可以使用不同的数据压缩。分区3 none是没有压缩的。
ALTER INDEX [PK_Address] ON [dbo].[Address]
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (3),
DATA_COMPRESSION = ROW ON PARTITIONS (2),
DATA_COMPRESSION = PAGE ON PARTITIONS (1)
)
GO
--以行方式压缩分区三(行压缩较久)
ALTER INDEX [PK_Address] ON [dbo].[Address]
REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = ROW ON PARTITIONS (3))
GO
--同样,重组也适合
ALTER INDEX [PK_Address] ON [dbo].[Address]
REORGANIZE PARTITION = 3
GO
ALTER INDEX [PK_Address] ON [dbo].[Address]
REORGANIZE PARTITION = ALL
GO