Sql Server 表分区

本文主要是介绍Sql Server 表分区，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

   sql server自2005开始支持分区特性，2012 以前单表分区数量限制1000个，2012开始限制数量为15000个，企业版才支分区特性,目前版本只支持范围分区一种，相比oracle 支持范围、列表、哈希以及子分区特性，功能还有不少的差距。

一、表分区的优势：

1、通过分区交换快速归档、清理历史数据，降低表数据量提升表访问性能；

2、通过分区裁决特性，当查询条件包含分区依据列时优化器可以直接跳过不包括条件数据的分区，降低io 提升查询速度；

3、当查询操作的数据位于某一分区时，sql server 可以只对该分区加锁而不用锁定全表，提升了表的并发性能

二、将普通表转换为分区表的方式：

1、新建新的空分区表采用insert into 插入数据方式

2、SSMS 创建分区向导方式

3、普通表上创建聚集分区索引方式

--唯一键索引分区限制条件

唯一聚集和非聚集分区索引必须包含分区列

三、分区交换进行大表数据迁移：

1、分区表taba分区数据切换到分区表tabb分区中

alter table dbo.taba partition 1 switch to dbo.tabb partition 1

alter table dbo.taba partition 2 switch to dbo.tabb partition 2

alter table dbo.taba partition 3 switch to dbo.tabb partition 3

2、分区表taba分区数据切换到非分区表tabb中

alter table dbo.taba partition 1 switch to dbo.tabb

3、非分区表tabb中数据切换到分区表taba分区中

ALTER TABLE dbo.tabb SWITCH TO dbo.taba PARTITION 1

--限制条件

分区表之间的分区切换必须是相同的表结构、索引，分区函数与分区架构必须一致

分区表分区到普通表之间的切换必须是相同的表结构、索引，普通表与待切换的分区表分区位于相同的文件组

普通表到分区表分区之间的切换必须是相同的表结构、索引，普通表与待切换的分区表分区位于相同的文件组，普通表存在与待交换分区分区键数据范围一致的check约束

四、分区操作：

1、创建分区函数

CREATE PARTITION FUNCTION [Func_Intime](datetime) AS RANGE LEFT FOR VALUES (N'2021-09-10T00:00:00.000', N'2021-12-10T00:00:00.000', N'2021-12-11T00:00:00.000', N'2021-12-12T00:00:00.000', N'2021-12-13T00:00:00.000', N'2021-12-14T00:00:00.000', N'2021-12-15T00:00:00.000', N'2021-12-16T00:00:00.000')

2、创建分区架构

CREATE PARTITION SCHEME [SCH_Intime] AS PARTITION [Func_Intime] TO ([FGLOG1], [FGLOG2], [FGLOG3], [FGLOG4], [FGLOG5], [FGLOG6], [FGLOG7], [FGLOG8], [FGLOG9])

3、创建分区表

create TABLE TABLE_name (col1 int, col2 varchar(100),col3 datetime,col4 nvarchar(10),

CONSTRAINT pk_col4 PRIMARY KEY NONCLUSTERED

(

col4，

col3

)

)

on SCH_Intime(col3);

4、创建与表对齐的分区索引

CREATE NONCLUSTERED INDEX [col1_idx] ON [dbo].[table_name]

(

col1

) on SCH_Intime(col3);

5、将主键索引分区，主键必须包括分区依据列

alter table TABLE_name drop CONSTRAINT pk_col4;

ALTER TABLE TABLE_name ADD

CONSTRAINT pk_col4 PRIMARY KEY NONCLUSTERED

     ( 

col4，

col3

     )

on SCH_Intime(col3);

6、将分区表的锁升级粒度调整为分区级而不用锁全表，提升分区表的并发性能

ALTER TABLE TABLE_name SET (LOCK_ECALATION = AUTOT)；

LOCK_ECALATION 有三个值，默认为TABLE：无能表是否分区，当发生锁升级时始终升级到表锁；

AUTO:当表为普通表时锁升级升级至表锁，如表为分区表时锁升级升级至分区锁；

DISABLE：在大多数情况下禁用表的锁升级，表级别的锁完全禁止。

7、表分区合并

ALTER PARTITION FUNCTION Func_Intime() MERGE RANGE (N'2021-09-10T00:00:00.000')

8、表分区扩充

ALTER PARTITION SCHEME SCH_Intime NEXT USED FGLOG10；

ALTER PARTITION FUNCTION Func_Intime() SPLIT RANGE (N'2021-12-17T00:00:00.000')；

9、truncate 分区数据，sql server 2016 开始支持

TRUNCATE TABLE TABLE_name

WITH(Partitions(1，2)) ； --清空1、2号分区数据

10、查询分区表上各个分区上的数据行数

SELECT $PARTITION.Func_Intime(datetime) AS Partition,  

COUNT(*) AS [COUNT] FROM dbo.TABLE_name

GROUP BY $PARTITION.Func_Intime(datetime)

ORDER BY Partition ; 

这篇关于Sql Server 表分区的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！