时间：2021-07-01 10:21:17 帮助过：3人阅读
GUI方法
分区函数
分区方案
上一张图有些人可能不懂，用PPT画张概念图：
创建脚本
系统生成脚本：
+ View Code?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
use [BigData_TestInfo_PartialData]
go
begin transaction
create partition function [CreatedatePartitionFun](varchar(10)) as range right for values(N‘2006-01-01‘, N‘2007-01-01‘, N‘2009-01-01‘, N‘2012-01-01‘)
create partition scheme [CreatedatePartitionScheme] as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary])
alter table [dbo].[Info] drop constraint [PK__Info__3214EC07B2FE10C8]
alter table [dbo].[Info] add primary key nonclustered
(
[Id] asc
)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
set ansi_padding on
create clustered index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info]
(
[CreateDate]
)with (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [CreatedatePartitionScheme]([CreateDate])
drop index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info]
commit transaction
go
|
命令方式创建（根据上面生成的命令逆推）
创建分区函数和架构（方案）
create partition function CreatedatePartitionFun(varchar(10)) as range right for values(N‘2006-01-01‘, N‘2007-01-01‘, N‘2009-01-01‘, N‘2012-01-01‘)
create partition scheme CreatedatePartitionScheme as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary])
创建分区表
尚未创建表的情况
已经创建了表（基本上都是这种情况）
主要就两步，把主键变为非聚集索引+创建分区聚集索引
alter table Info drop constraint PK__Info__3214EC064B338648
alter table Info add constraint PK_Info_Id primary key nonclustered (Id asc)
create clustered index IX_Info_CreateDate on Info(CreateDate) on CreatedatePartitionScheme(CreateDate)
测试：基本上是均匀分散在各个文件中，生产环境的时候可以把这些文件放各个磁盘
参考文章：
http://www.cnblogs.com/gaizai/p/3582024.html
http://www.cnblogs.com/lyhabc/p/3480917.html
http://www.cnblogs.com/libingql/p/4087598.html
http://www.cnblogs.com/CareySon/p/3252670.html
http://database.51cto.com/art/201009/225448.htm
http://www.cnblogs.com/knowledgesea/p/3696912.html
http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html
http://www.cnblogs.com/lykbk/p/erererert343243434388773437878.html
02.SQLServer性能优化之---水平分库扩展
标签：val data html pos lin use mit var led