SQLServer 重建重组表某个分区索引

概述测试： -- DROP TABLE [DemoDB].[dbo].[Address]SELECT * INTO [DemoDB].[dbo].[Address] FROM [AdventureWorks2014].[Person].[Address]GOUSE [DemoDB]GO--创建分区函数CREATE PARTITION FUNCTION [PF_ID](INT) AS

测试：

--	DROP table [DemoDB].[dbo].[Address]SELECT * INTO [DemoDB].[dbo].[Address] FROM [AdventureWorks2014].[Person].[Address]GOUSE [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_descFROM sys.Dm_db_index_physical_stats(Db_ID(),Object_ID('Address'),NulL,NulL)

--现在重建表第三个分区的索引，再看看，碎片减少了。ALTER INDEX [PK_Address] ON [dbo].[Address] REBUILD PARTITION = 3GO

--重建表所有分区的索引，可以使用不同的数据压缩。分区3 none是没有压缩的。ALTER INDEX [PK_Address] ON [dbo].[Address] REBUILD PARTITION = ALLWITH (	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 GOALTER INDEX [PK_Address] ON [dbo].[Address] REORGANIZE PARTITION = ALLGO

总结

以上是内存溢出为你收集整理的SQLServer 重建重组表某个分区索引全部内容，希望文章能够帮你解决SQLServer 重建重组表某个分区索引所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。