-
数据库
站-
热门城市 全国站>
-
其他省市
-
-
400-636-0069
小标
2018-07-18
来源 ：
阅读 4495
评论 0
摘要：本文主要向大家介绍了如何使用SQLServer数据库按月创建表分区，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了如何使用SQLServer数据库按月创建表分区，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。
比如有如下表需要以crt_time为标准按月分区
[sql] view plain copy
1. create table gf_card (
2. id bigint not null,
3. crt_time datetime not null,
4. name varchar(20) not null，
5. CONSTRAINT PK_card_id PRIMARY KEY (id asc)
6. )
(1).一般会将表按每个月的数据放入不同的文件组中，先给每个月建一个文件组
[sql] view plain copy
1. --创建文件组
2. alter database test add filegroup fileGroup01
3. alter database test add filegroup fileGroup02
4. alter database test add filegroup fileGroup03
5. alter database test add filegroup fileGroup04
6. alter database test add filegroup fileGroup05
7. alter database test add filegroup fileGroup06
8. alter database test add filegroup fileGroup07
9. alter database test add filegroup fileGroup08
10. alter database test add filegroup fileGroup09
11. alter database test add filegroup fileGroup10
12. alter database test add filegroup fileGroup11
13. alter database test add filegroup fileGroup12
如果要删除文件组可使用：
[sql] view plain copy
1. --删除文件组
2. alter database test remove filegroup fileGroup01
(2).一个文件组可以包含多个文件，为了提高性能文件一般放在多个不同的物理盘上，创建了文件组后需要指定这些文件组中包含哪些文件
[sql] view plain copy
1. ---创建数据文件到文件组
2. alter database test add file (name='test01',filename=N'D:\tmp\data\test01.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup01
3. alter database test add file (name='test02',filename=N'D:\tmp\data\test02.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup02
4. alter database test add file (name='test03',filename=N'D:\tmp\data\test03.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup03
5. alter database test add file (name='test04',filename=N'D:\tmp\data\test04.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup04
6. alter database test add file (name='test05',filename=N'D:\tmp\data\test05.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup05
7. alter database test add file (name='test06',filename=N'D:\tmp\data\test06.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup06
8. alter database test add file (name='test07',filename=N'D:\tmp\data\test07.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup07
9. alter database test add file (name='test08',filename=N'D:\tmp\data\test08.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup08
10. alter database test add file (name='test09',filename=N'D:\tmp\data\test09.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup09
11. alter database test add file (name='test10',filename=N'D:\tmp\data\test10.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup10
12. alter database test add file (name='test11',filename=N'D:\tmp\data\test11.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup11
13. alter database test add file (name='test12',filename=N'D:\tmp\data\test12.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup12
如果要删除数据文件到文件组可使用：
[sql] view plain copy
1. ---删除数据文件到文件组
2. alter database test remove file test01
(3). 分区函数是为了划分分区边界，range left表示{，value]，(value，}，range right表示{，value)，[value，}
[sql] view plain copy
1. --创建分区函数
2. create partition function [PF_card] (datetime) as range right for values
3. ('2017-01-01','2017-02-01','2017-03-01','2017-04-01','2017-05-01','2017-06-01',
4. '2017-07-01','2017-08-01','2017-09-01','2017-10-01','2017-11-01','2017-12-01')
如果要删除分区函数可使用：
[sql] view plain copy
1. --删除分区函数
2. drop partition function [PF_card]
(4).有了分区函数后，需要指定不同分区的数据放到哪个文件组中
[sql] view plain copy
1. --创建分区方案
2. CREATE PARTITION SCHEME [PS_card] AS PARTITION [PF_card]
3. TO ([PRIMARY], [fileGroup01], [fileGroup02], [fileGroup03], [fileGroup04], [fileGroup05],
4. [fileGroup06], [fileGroup07], [fileGroup08], [fileGroup09], [fileGroup10], [fileGroup11], [fileGroup12])
如果要删除分区方案
[sql] view plain copy
1. --删除分区方案
2. drop PARTITION SCHEME [PS_card]
(5).上面创建了2017年12个月的分区，如果要添加2018年12个月的分区并且复用fileGroup01~fileGroup12
[sql] view plain copy
1. --修改边界值
2. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup01]
3. alter partition function [PF_card]() SPLIT RANGE ('2018-01-01')
4.
5. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup02]
6. alter partition function [PF_card]() SPLIT RANGE ('2018-02-01')
7.
8. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup03]
9. alter partition function [PF_card]() SPLIT RANGE ('2018-03-01')
10.
11. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup04]
12. alter partition function [PF_card]() SPLIT RANGE ('2018-04-01')
13.
14. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup05]
15. alter partition function [PF_card]() SPLIT RANGE ('2018-05-01')
16.
17. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup06]
18. alter partition function [PF_card]() SPLIT RANGE ('2018-06-01')
19.
20. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup07]
21. alter partition function [PF_card]() SPLIT RANGE ('2018-07-01')
22.
23. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup08]
24. alter partition function [PF_card]() SPLIT RANGE ('2018-08-01')
25.
26. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup09]
27. alter partition function [PF_card]() SPLIT RANGE ('2018-09-01')
28.
29. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup10]
30. alter partition function [PF_card]() SPLIT RANGE ('2018-10-01')
31.
32. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup11]
33. alter partition function [PF_card]() SPLIT RANGE ('2018-11-01')
34.
35. ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup12]
36. alter partition function [PF_card]() SPLIT RANGE ('2018-12-01')
(6) .按分区创建表和索引
[sql] view plain copy
1. create table gf_card (
2. id bigint not null,
3. crt_time datetime not null,
4. name varchar(20) not null
5. ) on [PS_card] (crt_time)
对于分区表，如果要建主键，必须把分区依据列crt_time包含在内，同时如果要建唯一索引也要把分区依据列crt_time包含在内，所以分区表不能再单独对id建主键，也不能单独对id建唯一索引，但可以建非唯一索引
[sql] view plain copy
1. -- 将索引放在[PRIMARY]文件组中
2. CREATE CLUSTERED INDEX [IX_card_id] ON gf_card (id) ON [PRIMARY]
由于crt_time查询时经常会用到，所以在crt_time上也建索引，并且也将各分区的索引放入各分区中
[sql] view plain copy
1. CREATE NONCLUSTERED INDEX [IX_card_crt_time] ON gf_card (crt_time) on [PS_card] (crt_time)
(7).插入一些数据测试
[sql] view plain copy
1. insert into gf_card (id, crt_time, name) values (99, '2017-07-01', 'n99')
2. insert into gf_card (id, crt_time, name) values (100, '2017-07-01', 'n100')
3. insert into gf_card (id, crt_time, name) values (101, '2017-07-01', 'n101')
4. insert into gf_card (id, crt_time, name) values (200, '2017-07-02', 'n102')
5. insert into gf_card (id, crt_time, name) values (600, '2017-07-01', 'n600')
查询表中数据在哪个分区上
[sql] view plain copy
1. select *, $partition.PF_card(crt_time) partitionNum from gf_card
id crt_time name partitionNum
99 2017-01-01 00:00:00.000 n99 2
100 2017-07-01 00:00:00.000 n100 8
101 2017-11-01 00:00:00.000 n101 12
200 2018-01-02 00:00:00.000 n102 14
600 2018-07-01 00:00:00.000 n600 20
从结果中可以看到fileGroup01的分区号为2，fileGroup07的分区号为8，primary的分区号自然就为1 了
[sql] view plain copy
1. --查询数据在哪个分区上
2. select $partition.[PF_card]('2017-07-01')
[sql] view plain copy
1. --查询表中数据在哪个分区上
2. select $partition.[PF_card](id)
3. from gf_card
[sql] view plain copy
1. -- 查询表有哪些分区
2. SELECT *
3. FROM sys.partitions AS p JOIN sys.tables AS t ON p.object_id = t.object_id
4. WHERE p.partition_id IS NOT NULL
5. AND t.name = 'gf_card';
(8).将分区中的数据删除
比如只删除fileGroup01中的数据，由于直接使用truncate删除分区中的数据要到SQL server2016才支持，所以使用一种通用的方法来删除。
创建一个与gf_card列相同的表gf_card_tmp，并且此表也要建在fileGroup01中
[sql] view plain copy
1. create table gf_card_tmp (
2. id bigint not null,
3. crt_time datetime not null,
4. name varchar(20) not null
5. ) on [fileGroup01]
将分区2中的数据转移到gf_card_tmp中，此时gf_card分区2中就没有数据了
[sql] view plain copy
1. alter table gf_card switch partition 2 to gf_card_tmp
删除gf_card_tmp中的数据
[sql] view plain copy
1. truncate table gf_card_tmp
(9).如何查询库中有哪些PartitionScheme和PartitionFunction？
[sql] view plain copy
1. select ps.Name PartitionScheme, pf.name PartitionFunction
2. from sys.indexes i
3. join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
4. join sys.partition_functions pf on pf.function_id = ps.function_id
5. where i.object_id = object_id('gf_card')
本文由职坐标整理并发布，了解更多内容，请关注职坐标SQL Server数据库频道！
喜欢 | 0
不喜欢 | 0
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了，明天再试试吧！
我们会在第一时间安排职业规划师联系您！
您也可以联系我们的职业规划师咨询：
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com，All Rights Reserved.
沪公网安备 31011502005948号
索取资料
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈
