概念就不多说了,网上大把.
--分区函数的作用是告诉SQL Server,如何将数据进行分区,例如按时间,按年龄,按省.
--而分区方案的作用则是告诉SQL Server,将已分区的数据放在哪个文件组中. 多个硬盘上分开选择的文件组更好.
一,用语句
创建,分区方案:
- CREATE PARTITION SCHEME YearCustomerFollow AS PARTITION
- YearCustomerFollowFunction TO([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])
创建,分区函数:
- CREATE PARTITION FUNCTION YearCustomerFollowFunction(datetime)
- AS RANGE RIGHT FOR VALUES('20100101','20110101','20120101','20130101')
删除一个分区:
- ALTER PARTITION FUNCTION YearCustomerFollowFunction() MERGE RANGE ('20100101')
添加一个分区:
- ALTER PARTITION SCHEME YearCustomerFollow NEXT USED [PRIMARY] -- 分区方案
- ALTER PARTITION FUNCTION YearCustomerFollowFunction() SPLIT RANGE ('20090101') -- 分界值
创建分区:(这里的语句可以用向导生成更方便)
- USE [SQL_ZZW]
- GO
- BEGIN TRANSACTION
- CREATE CLUSTERED INDEX [ClusteredIndex_on_YearCustomerFollow_01] ON [dbo].[ZZW_test]
- (
- [birthyday]
- )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [YearCustomerFollow]([birthyday])
- DROP INDEX [ClusteredIndex_on_YearCustomerFollow_01] ON [dbo].[ZZW_test] WITH ( ONLINE = OFF )
查询分区表行数.
- select $PARTITION.YearCustomerFollowFunction(birthyday) as 分区编号,count(id) as 记录数
- from zzw_test
- group by $PARTITION.YearCustomerFollowFunction(birthyday)
二,用向导
1,在要修改的表上右键->存储->创建分区.
2,选择分区列.
3,创建分区函数或选择以后的分区函数.
4,创建分区方案或选择现有分区方案.
5,映射分区,先设置边界.
6,可以看到边界值都一下子已经设置好了.
7,创建完成.
查看:表右键->存储-管理压缩.可以看到各分区的行数,空间.
在控制台库->存储下可以直接看到刚创建的分区方案和分区函数.
