第一步、创建文件和文件组
-- 准备工作，模拟数据。
insert into
test
values
('test','2014-01-01') ,
('test','2014-02-01') ,
('test','2014-03-01') ,
('test','2014-04-01') ,
('test','2014-05-01') ,
('test','2014-06-01') ,
('test','2014-07-01') ,
('test','2014-08-01') ,
('test','2014-09-01') ,
('test','2014-10-01')
## -- 1、添加文件组
alter database User_DB add filegroup know1
alter database User_DB add filegroup know2
alter database User_DB add filegroup know3
## -- 2、创建数据库文件到文件组
alter database User_DB add file
(name=N'know1',filename=N'G:\DB\fenqu\know1.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)
to filegroup know1
alter database User_DB add file
(name=N'know2',filename=N'G:\DB\fenqu\know2.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)
to filegroup know2
alter database User_DB add file
(name=N'know3',filename=N'G:\DB\fenqu\know3.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)
to filegroup know3
##
--3、右键到要分区的表>> 存储--- >> 创建分区--- >>显示向导视图--- >> 下一步--- >> 下一步
# 第二步、执行生成的创建分区sql
USE [User_DB]
GO
BEGIN TRANSACTION
## -- 1、创建分区函数
CREATE PARTITION FUNCTION [knowPartitionFunc](datetime)
AS RANGE
LEFT FOR VALUES (N'2014-01-01T00:00:00', N'2014-02-01T00:00:00', N'2014-03-01T00:00:00')
## -- 2、创建分区解决方案
CREATE PARTITION SCHEME [konwPartitionSolution]
AS PARTITION [knowPartitionFunc] TO ([PRIMARY], [know1], [know2], [know3])
## -- 3、创建索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_konwPartitionSolution_635706905441367386]
ON [dbo].[test]
(
[time]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [konwPartitionSolution]([time])
## -- 4、删除索引
DROP INDEX
[ClusteredIndex_on_konwPartitionSolution_635706905441367386]
ON [dbo].[test] WITH ( ONLINE = OFF )
COMMIT TRANSACTION
# 第三步、查看分区结果
## --查看分区表中，每个非空分区存在的行数
select $partition.[knowPartitionFunc](time) as partitionNum,count(*) as recordCount
from test
group by $partition.[knowPartitionFunc] (time)
## --查看分区数据
select $partition.[knowPartitionFunc](time) as partitionGroupId,*
from test
# 第四步、为已存在的分区表添加新的边界值
## --1、创建文件组和对应的文件。
alter database User_DB add filegroup know4
alter database User_DB add file
(name=N'know4',filename=N'G:\DB\fenqu\know4.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)
to filegroup know4
## --2、给分区方案添加文件组
alter partition scheme konwPartitionSolution
NEXT USED know4
## --3、给分区函数添加一个边界值
alter partition function knowPartitionFunc()
split range(N'2014-04-01T00:00:00')
