SQL Server 表分区
目的：把数据按照规则存放在不同的文件中，提升查询速度。
如何创建分区表(假设数据库名：webDB)一、创建文件组其实可以使用默认的primary组，但是为了更方便管理以及提高运行速度，所以还是应该创建几个分组。
1、使用SSMS创建文件组
--alter database <数据库名> add filegroup <文件组名>alter database webDB add filegroup group2013alter database webDB add filegroup group2014alter database webDB add filegroup group2015二、为文件组添加数据库文件1、使用SSMS添加数据库文件
--alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>alter database webDB add file( name=‘web2013‘, filename=‘D:web2013.ndf‘, size=5mb, filegrowth=5mb)to filegroup group2013alter database webDB add file( name=‘web2014‘, filename=‘D:web2014.ndf‘, size=5mb, filegrowth=5mb)to filegroup group2014alter database webDB add file( name=‘web2015‘, filename=‘D:web2015.ndf‘, size=5mb, filegrowth=5mb)to filegroup group2015
注意：尽可能的将不同的文件放在不同的硬盘分区里，或者独立硬盘中。这样可以加快SQL Server运行速度。
三、创建分区函数分区函数用来告诉SQL Server用什么样的规则进行分区，这一步必须使用T-SQL脚本来执行了。
create partition function fenqu(datetime) --分区函数名as range right --right分区方式 边界值去左表还是右表for values (‘2014-01-01‘,‘2015-01-01‘) --按这些值来分区 --group2013 : 2014-01-01 之前的--group2014 : 2014-01-01 到 2014-12-31的--group2015 : 2015-01-01 之后的
四、创建分区方案create partition scheme SchemeFenqu --分区方案名as partition fenqu --之前创建的分区函数to(group2013,group2014,group2015) --跟放的文件组
创建完分区函数和分区方案后可以在存储中查看
create table fenquTable( id int identity(1,1) not null, name varchar(20) not null, createTime datetime not null) on SchemeFenqu(createTime) --调用分区方案
注意：不可以使用聚集索引，因为聚集索引是存在连续的物理地址中的，而表分区是将数据分别存储在不同表中的。
至此物理上分离的，逻辑上一体的分区表就创建完了。
--插入测试数据insert into fenquTable(name,createTime) values (‘隔壁老王‘,‘2010-01-01‘)insert into fenquTable(name,createTime) values (‘隔壁老张‘,‘2011-01-01‘)insert into fenquTable(name,createTime) values (‘隔壁老赵‘,‘2012-01-01‘)insert into fenquTable(name,createTime) values (‘隔壁老李‘,‘2013-01-01‘)insert into fenquTable(name,createTime) values (‘老李儿子‘,‘2013-10-01‘)insert into fenquTable(name,createTime) values (‘隔壁老田‘,‘2014-01-01‘)insert into fenquTable(name,createTime) values (‘隔壁老梁‘,‘2015-01-01‘)insert into fenquTable(name,createTime) values (‘老梁姑娘楠楠‘,‘2015-10-10‘)
跟插入普通表没有任何区别，不用管他放在哪个物理磁盘上。
二、查询数据--查询数据select * from fenquTable
查询也是如此，不用考虑哪个磁盘，逻辑上都属于同一个表，基本上看不出区别。如果需要查看哪条插入到哪个物理的分区表中，可以使用$partition函数查看。
--语法：$partition.分区函数名(表达式)--查看该表达式下有多少数据select $partition.fenqu(‘2015-01-01‘) --返回3--查看分区表明细select * from fenquTable where $partition.fenqu(createTime)=1select * from fenquTable where $partition.fenqu(createTime)=2select * from fenquTable where $partition.fenqu(createTime)=3--查看分区表中的记录数select $partition.fenqu(createTime) as 分区 ,count(id) from fenquTable group by $partition.fenqu(createTime)
三、修改数据select $partition.fenqu(createTime) as 分区,count(id) as 数量 from fenquTablegroup by $partition.fenqu(createTime)--分区 数量--1 5--2 1--3 2update fenquTable set createTime =‘2015-01-01‘ where id = 1select $partition.fenqu(createTime) as 分区,count(id) as 数量 from fenquTablegroup by $partition.fenqu(createTime)--分区 数量--1 4--2 1--3 3
可以明显看到，跟普通修改没有区别，SQL Server可以自动帮我们重新划分分区，将数据从第一个分区移动到第五个分区中。
普通表转分区表上面介绍了如何在创建表的时候进行分区，但往往我们需要的是将现有的普通表在数据保留的情况下进行分区。
普通表一般都有主键，同时还是聚集索引。分区是以某个字段为条件进行的，而除了这个字段其他字段是不可以创建聚集索引的。所以需要先删除表中的聚集索引，再新建一个聚集索引。
--删除主键，自动同时删除索引alter table newTable drop constraint PK_newTable --创建主键，但不创建聚集索引alter table newTable add constraint PK_newTableprimary key nonclustered --非聚集( id asc) on [primary]--然后给我们亲爱的时间创建一个聚集索引create clustered index CT_newTable on newTable(createTime)on schemeFenqu(createTime) --并调用分区方案--然后再查询分区，发现数据保留情况下，已经将数据按规则进行分区了select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)
添加分区向上面只分了3个区，而15年以后的都存在第三个分区中，到16年还是会存在这个分区中，这时候需要再新加一个16年的分区。
添加新的分区意味着要新建一个文件组和文件来存放这个分区表，然后在分区方案中用到这个文件组，最后再修改一下分区函数的规则即可。文件组和分区数量要保持一致。
--创建文件组alter database webDB add filegroup group2016--添加数据库文件alter database webDB add file( name=‘web2016‘, filename=‘D:web2016.ndf‘, size=5mb, filegrowth=5mb)to filegroup group2016--修改分区方案alter partition scheme SchemeFenqunext used group2016--修改分区函数alter partition function fenqu()split range(‘2016-01-01‘)--添加2016年数据insert into newTable (name,createTime) values (‘16年小明‘,‘2016-03-05‘)--查看分区及统计select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)
删除分区删除分区就是将分区函数中多余的边界值删除。
如：2013，2014，2015，2016 现在需要将13年和14年进行合并，删除13年的分区。
--查看分区及统计select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)--分区 数量--1 4--2 1--3 3--4 1--删掉该边界值alter partition function fenqu() merge range(‘2014-01-01‘)--再次查询select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)--分区 数量--1 5--2 3--3 1
拆分分区有的时候某一分区中数据量过大，需要将这个分区再次拆分为多个分区，以加快访问速度。
拆分分区的操作其实与添加分区类似，首先要添加文件组、文件、修改分区方案、修改分区函数(新增一个边界值)。
如：2014，2015，2015年6月份以上一个 6月份一下一个。
--查看分区及统计select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)--分区 数量--1 5--2 3--3 1--创建文件组alter database webDB add filegroup group2014_2015--添加数据库文件alter database webDB add file( name=‘web2014_2015‘, filename=‘D:web2014_2015.ndf‘, size=5mb, filegrowth=5mb)to filegroup group2014_2015--修改分区方案alter partition scheme SchemeFenqunext used group2014_2015--修改分区函数alter partition function fenqu()split range(‘2015-06-01‘)--查看分区及统计select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)--分区 数量--1 5--2 2--3 1--4 1
分区表转普通表--修改分区函数 将边界值都删除alter partition function fenqu()merge range(‘2013-01-01‘)alter partition function fenqu()merge range(‘2014-01-01‘)alter partition function fenqu()merge range(‘2015-01-01‘)alter partition function fenqu()merge range(‘2015-06-01‘)select $partition.fenqu(createTime) as 分区,count(id) as 数量from newTable group by $partition.fenqu(createTime)--这时只有一个分区了--分区 数量--1 9
这样虽然只有一个分区了，但是查看数据表存储位置，是否进行分区：True，分区数1.
--重新建立聚集索引create clustered index CT_newTable on newTable(createTime) with(drop_existing=on) --如果存在则删除on [primary]
删除分区索引后，重新建立聚集索引，这时再此查看数据表的存储位置，是否分区：Flase。
SQL Server 表分区
标签：
小编还为您整理了以下内容，可能对您也有帮助：
如何对一个已经存在的SQL Server表分区
有两种方法可以实现对一个表分区.一是创建一个新的标识为分区表的表(你可参照此步骤),然后把数据复制到这张新表,再对这两张表分别改名.或者,像我写在下面的,通过重建或创建一个聚集索引来达到分区一个表.
一个SQL Server表和数据进行分区示例
--Table/Index creation
CREATE TABLE [dbo].[TABLE1]
([pkcol] [int] NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] [varchar](50) NULL,
[partitioncol] datetime)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol)
GO
CREATE NONCLUSTERED INDEX IX_TABLE1_col2col3 ON dbo.TABLE1 (datacol1,datacol2)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
SQL server 表分区问题,大家帮忙看看吧,谢啦
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组USE [master]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]GO-- 创建和文件组相对应的文件，由于只有3个盘USE [master]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]GO
注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看SQL Server表分区。
2.建立相应的分区函数和分区方案
USE TClientLog;CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)AS RANGE right FOR VALUES (‘2015-08-01 00:00:00‘,‘2015-08-02 00:00:00‘,‘2015-08-03 00:00:00‘,‘2015-08-04 00:00:00‘,‘2015-08-05 00:00:00‘,‘2015-08-06 00:00:00‘,‘2015-08-07 00:00:00‘,‘2015-08-08 00:00:00‘,‘2015-08-09 00:00:00‘,‘2015-08-10 00:00:00‘,‘2015-08-11 00:00:00‘,‘2015-08-12 00:00:00‘,‘2015-08-13 00:00:00‘,‘2015-08-14 00:00:00‘,‘2015-08-15 00:00:00‘,‘2015-08-16 00:00:00‘,‘2015-08-17 00:00:00‘,‘2015-08-18 00:00:00‘,‘2015-08-19 00:00:00‘,‘2015-08-20 00:00:00‘,‘2015-08-21 00:00:00‘,‘2015-08-22 00:00:00‘,‘2015-08-23 00:00:00‘,‘2015-08-24 00:00:00‘,‘2015-08-25 00:00:00‘,‘2015-08-26 00:00:00‘,‘2015-08-27 00:00:00‘,‘2015-08-28 00:00:00‘,‘2015-08-29 00:00:00‘,‘2015-08-30 00:00:00‘,‘2015-08-31 00:00:00‘);CREATE PARTITION SCHEME part_day_rang_schemeAS PARTITION part_day_rang_funcTO (FGDAY00,FGDAY01,FGDAY02,FGDAY03,FGDAY04,FGDAY05,FGDAY06,FGDAY07,FGDAY08,FGDAY09,FGDAY10,FGDAY11,FGDAY12,FGDAY13,FGDAY14,FGDAY15,FGDAY16,FGDAY17,FGDAY18,FGDAY19,FGDAY20,FGDAY21,FGDAY22,FGDAY23,FGDAY24,FGDAY25,FGDAY26,FGDAY27,FGDAY28,FGDAY29,FGDAY30,FGDAY31);
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog]( [SynID] [nchar](38) NOT NULL, [ParkingId] [int] NOT NULL, [ParkingBoxId] [int] NOT NULL, [Message] [varchar](max) NULL, [OccurTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [ErrorLevel] [int] NOT NULL, [State] [int] NULL, [IsSend] [int] NULL, CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED ( [SynID] ASC, [OccurTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])) ON [part_day_rang_scheme]([OccurTime])GOCREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] ( [ParkingId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])GO
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================-- Author: zhangkun-- Create date: <2015.08.07>-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>-- =============================================-- 1.修改分区方案和分区函数-- 2.进行分区交换，将归档数据放入历史表alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIMEAS BEGIN DECLARE @flag CHAR(1) --标志位 IF @td IS NULL --如果@td为null，则默认当天 SET @td = GETDATE()-- 1.修改分区方案和分区函数,当天新增后数第七天的日期 BEGIN DECLARE @td_next7 DATETIME DECLARE @day_next7 VARCHAR(2) DECLARE @sql NVARCHAR(MAX) --动态sql字符串 SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期 SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1 THEN ‘0‘ + DATENAME(DAY, @td_next7) ELSE DATENAME(DAY, @td_next7) END; --7天后是当月第几天SELECT @flag = COUNT(1) FROM sys.partition_functions a , sys.partition_range_values b WHERE a.name = ‘part_day_rang_func‘ AND a.function_id = b.function_id AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120) + ‘ 00:00:00.000‘; PRINT @flag; IF ( @flag != ‘1‘ ) BEGIN SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘ + @day_next7 + ‘; alter partition function part_day_rang_func() split range(‘‘‘ + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ EXEC sp_executesql @sql; END END -- 2.进行分区交换，将归档数据放入历史表 DECLARE @td_before7 DATETIME DECLARE @day_before7 VARCHAR(2) SET @td_before7 = DATEADD(DAY, -7SQL server 表分区问题,大家帮忙看看吧,谢啦
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组USE [master]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]GO-- 创建和文件组相对应的文件，由于只有3个盘USE [master]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]GO
注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看SQL Server表分区。
2.建立相应的分区函数和分区方案
USE TClientLog;CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)AS RANGE right FOR VALUES (‘2015-08-01 00:00:00‘,‘2015-08-02 00:00:00‘,‘2015-08-03 00:00:00‘,‘2015-08-04 00:00:00‘,‘2015-08-05 00:00:00‘,‘2015-08-06 00:00:00‘,‘2015-08-07 00:00:00‘,‘2015-08-08 00:00:00‘,‘2015-08-09 00:00:00‘,‘2015-08-10 00:00:00‘,‘2015-08-11 00:00:00‘,‘2015-08-12 00:00:00‘,‘2015-08-13 00:00:00‘,‘2015-08-14 00:00:00‘,‘2015-08-15 00:00:00‘,‘2015-08-16 00:00:00‘,‘2015-08-17 00:00:00‘,‘2015-08-18 00:00:00‘,‘2015-08-19 00:00:00‘,‘2015-08-20 00:00:00‘,‘2015-08-21 00:00:00‘,‘2015-08-22 00:00:00‘,‘2015-08-23 00:00:00‘,‘2015-08-24 00:00:00‘,‘2015-08-25 00:00:00‘,‘2015-08-26 00:00:00‘,‘2015-08-27 00:00:00‘,‘2015-08-28 00:00:00‘,‘2015-08-29 00:00:00‘,‘2015-08-30 00:00:00‘,‘2015-08-31 00:00:00‘);CREATE PARTITION SCHEME part_day_rang_schemeAS PARTITION part_day_rang_funcTO (FGDAY00,FGDAY01,FGDAY02,FGDAY03,FGDAY04,FGDAY05,FGDAY06,FGDAY07,FGDAY08,FGDAY09,FGDAY10,FGDAY11,FGDAY12,FGDAY13,FGDAY14,FGDAY15,FGDAY16,FGDAY17,FGDAY18,FGDAY19,FGDAY20,FGDAY21,FGDAY22,FGDAY23,FGDAY24,FGDAY25,FGDAY26,FGDAY27,FGDAY28,FGDAY29,FGDAY30,FGDAY31);
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog]( [SynID] [nchar](38) NOT NULL, [ParkingId] [int] NOT NULL, [ParkingBoxId] [int] NOT NULL, [Message] [varchar](max) NULL, [OccurTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [ErrorLevel] [int] NOT NULL, [State] [int] NULL, [IsSend] [int] NULL, CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED ( [SynID] ASC, [OccurTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])) ON [part_day_rang_scheme]([OccurTime])GOCREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] ( [ParkingId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])GO
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================-- Author: zhangkun-- Create date: <2015.08.07>-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>-- =============================================-- 1.修改分区方案和分区函数-- 2.进行分区交换，将归档数据放入历史表alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIMEAS BEGIN DECLARE @flag CHAR(1) --标志位 IF @td IS NULL --如果@td为null，则默认当天 SET @td = GETDATE()-- 1.修改分区方案和分区函数,当天新增后数第七天的日期 BEGIN DECLARE @td_next7 DATETIME DECLARE @day_next7 VARCHAR(2) DECLARE @sql NVARCHAR(MAX) --动态sql字符串 SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期 SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1 THEN ‘0‘ + DATENAME(DAY, @td_next7) ELSE DATENAME(DAY, @td_next7) END; --7天后是当月第几天SELECT @flag = COUNT(1) FROM sys.partition_functions a , sys.partition_range_values b WHERE a.name = ‘part_day_rang_func‘ AND a.function_id = b.function_id AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120) + ‘ 00:00:00.000‘; PRINT @flag; IF ( @flag != ‘1‘ ) BEGIN SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘ + @day_next7 + ‘; alter partition function part_day_rang_func() split range(‘‘‘ + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ EXEC sp_executesql @sql; END END -- 2.进行分区交换，将归档数据放入历史表 DECLARE @td_before7 DATETIME DECLARE @day_before7 VARCHAR(2) SET @td_before7 = DATEADD(DAY, -7sql server怎么建立分区表打开MsSQL2008,找到作业该项,如果打不开或者SQL Server代理是未启动状态,请先在windows服务中启动SQL Server代理(参考图片),
2
右击MsSQL2008对象资源管理器中的作业,选择新建作业,输入该作业你想用的名称,类别不用管,说明里面是输入一些该作业完成的功能,可不写,请务必勾选已启用复选框.
3
点击新建作业窗体左侧的步骤项,点击右侧区域下方的新建按钮,输入步骤名称,类型请选择Transact-SQL脚本(T-SQL),运行身份默认,数据库请选择要进行分区的数据库,请不要选择master默认的,命令文本框中输入如下代码:
/*--------------------创建数据库的文件组和物理文件------------------------*/
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)
set @tableName='要分区的数据库名称'
set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间
set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)
set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')
set @fileGroupName=N'G'+@newNameStr
set @ndfName=N'F'+@newNameStr+''
set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'.ndf'
set @partFunName=N'pf_Time'
set @schemeName=N'ps_Time'
--创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区方案'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
print '修改分区函数'
end
end
--创建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',
FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
print '新创建ndf文件'
end
/*--------------------以上创建数据库的文件组和物理文件------------------------*/
--分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此处修改需要在修改分区函数之前执行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay
+''')')
print '新创建分区函数'
end
--分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此处修改需要在修改分区方案之前执行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO
(''PRIMARY'','''+@fileGroupName+''')')
print '新创建分区方案'
end
print '---------------以下是变量定义值显示---------------------'
print '当前数据库:'+@tableName
print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'
print '合法命名方式:'+@newNameStr
print '文件组名称:'+@fileGroupName
print 'ndf物理文件名称:'+@ndfName
print '物理文件完整路径:'+@fullPath
print '分区函数:'+@partFunName
print '分区方案:'+@schemeName
/*
--查看创建的分区函数
select * from sys.partition_functions
--查看分区函数的临界值
select * from sys.partition_range_values
--查询分区方案
select * from sys.partition_schemes
--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据
select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1
*/
GO
点击确定按钮
上述代码中的变量名称,路径等均可自行修改,上述是按天为单位,以G开头的日期作为文件组名称,以F开头的日期作为物理分区文件名即ndf文件名称
4
选择新建分区左侧的计划项,然后点击右侧区域下方的新建按钮,设定新建分区的时间间隔,图中设置的是每天创建一个新的分区,用户也可以自行修改,按月,按周,按自定义时间等
其他的条目,通知,警报,目标可自行设置,也可不设置,至此自动创建分区的计划任务已成功设置.
END
步骤二:对表应用分区方案和分区函数
右击要分区的表,选择存储菜单下的创建分区,上述步骤一中创建的分区函数是按datetime类型进行的分区,所以创建分区的时候需要选择相应类型的字段作为分区依据,用户也可以根据int型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行.
需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可.sql server怎么建立分区表
打开MsSQL2008,找到作业该项,如果打不开或者SQL Server代理是未启动状态,请先在windows服务中启动SQL Server代理(参考图片),
2
右击MsSQL2008对象资源管理器中的作业,选择新建作业,输入该作业你想用的名称,类别不用管,说明里面是输入一些该作业完成的功能,可不写,请务必勾选已启用复选框.
3
点击新建作业窗体左侧的步骤项,点击右侧区域下方的新建按钮,输入步骤名称,类型请选择Transact-SQL脚本(T-SQL),运行身份默认,数据库请选择要进行分区的数据库,请不要选择master默认的,命令文本框中输入如下代码:
/*--------------------创建数据库的文件组和物理文件------------------------*/
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)
set @tableName='要分区的数据库名称'
set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间
set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)
set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')
set @fileGroupName=N'G'+@newNameStr
set @ndfName=N'F'+@newNameStr+''
set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'.ndf'
set @partFunName=N'pf_Time'
set @schemeName=N'ps_Time'
--创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区方案'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
print '修改分区函数'
end
end
--创建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',
FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
print '新创建ndf文件'
end
/*--------------------以上创建数据库的文件组和物理文件------------------------*/
--分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此处修改需要在修改分区函数之前执行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay
+''')')
print '新创建分区函数'
end
--分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此处修改需要在修改分区方案之前执行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO
(''PRIMARY'','''+@fileGroupName+''')')
print '新创建分区方案'
end
print '---------------以下是变量定义值显示---------------------'
print '当前数据库:'+@tableName
print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'
print '合法命名方式:'+@newNameStr
print '文件组名称:'+@fileGroupName
print 'ndf物理文件名称:'+@ndfName
print '物理文件完整路径:'+@fullPath
print '分区函数:'+@partFunName
print '分区方案:'+@schemeName
/*
--查看创建的分区函数
select * from sys.partition_functions
--查看分区函数的临界值
select * from sys.partition_range_values
--查询分区方案
select * from sys.partition_schemes
--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据
select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1
*/
GO
点击确定按钮
上述代码中的变量名称,路径等均可自行修改,上述是按天为单位,以G开头的日期作为文件组名称,以F开头的日期作为物理分区文件名即ndf文件名称
4
选择新建分区左侧的计划项,然后点击右侧区域下方的新建按钮,设定新建分区的时间间隔,图中设置的是每天创建一个新的分区,用户也可以自行修改,按月,按周,按自定义时间等
其他的条目,通知,警报,目标可自行设置,也可不设置,至此自动创建分区的计划任务已成功设置.
END
步骤二:对表应用分区方案和分区函数
右击要分区的表,选择存储菜单下的创建分区,上述步骤一中创建的分区函数是按datetime类型进行的分区,所以创建分区的时候需要选择相应类型的字段作为分区依据,用户也可以根据int型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行.
需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可.sqlserver查看哪些表有分区
SQLSERVER中，有时需要知道已经建了哪些分区表，从哪里看？
1、直接用SQL语句查：
--分区数大于1的，就是我们想查看的分区表：
SELECT p.*,'|' AS SP ,t.* FROM sys.partitions AS p
inner JOIN sys.tables AS t ON p.object_id = t.object_id
inner join (
select object_id as object_id2,index_id, count(*) AS CNT FROM sys.partitions
group by object_id,index_id
having count(*)>1
) s on s.object_id2 = p.object_id
WHERE p.partition_id IS NOT NULL
order by t.name;
2、在管理器中查看:
Databases > [数据库名称] 节点 > 存储(Storage) 节点 >
\ 分区架构(Partition Schemes ) 节点 > 选中一项 > 右键 > 查看依赖
\ 分区函数(Partition Functions ) 节点 > 选中一项 > 右键 > 生成脚本
3、有作多分区的表，属性的 存储(Storage) 会有 Partitioning 信息。sqlserver查看哪些表有分区
SQLSERVER中，有时需要知道已经建了哪些分区表，从哪里看？
1、直接用SQL语句查：
--分区数大于1的，就是我们想查看的分区表：
SELECT p.*,'|' AS SP ,t.* FROM sys.partitions AS p
inner JOIN sys.tables AS t ON p.object_id = t.object_id
inner join (
select object_id as object_id2,index_id, count(*) AS CNT FROM sys.partitions
group by object_id,index_id
having count(*)>1
) s on s.object_id2 = p.object_id
WHERE p.partition_id IS NOT NULL
order by t.name;
2、在管理器中查看:
Databases > [数据库名称] 节点 > 存储(Storage) 节点 >
\ 分区架构(Partition Schemes ) 节点 > 选中一项 > 右键 > 查看依赖
\ 分区函数(Partition Functions ) 节点 > 选中一项 > 右键 > 生成脚本
3、有作多分区的表，属性的 存储(Storage) 会有 Partitioning 信息。sqlserver中分表和分库有什么区别
MS SQL Server：分区表、分区索引 详解
1. 分区表简介
使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。
大型表：数据量巨大的表。
访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。
分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。
注意：只能在 SQL Server Enterprise Edition 中创建分区函数。只有 SQL Server Enterprise Edition 支持分区。
2. 创建分区表或分区索引的步骤
可以分为以下步骤：
1. 确定分区列和分区数
2. 确定是否使用多个文件组
3. 创建分区函数
4. 创建分区架构（Schema）
5. 创建分区表
6. 创建分区索引
下面详细描述的创建分区表、分区索引的步骤。
2.1. 确定分区列和分区数
在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，就可以把日期作为分区列。
确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定在哪个范围。
2.2. 确定是否使用多个文件组
为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个CPU，则SQL Server 可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。
2.3. 创建分区函数
分区函数用于定义分区的边界条件，创建分区函数的语法如下：
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]
参数说明：
partition_function_name
是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。
input_parameter_type
是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。
实际列（也称为分区列）是在 CREATE TABLE 或 CREATE INDEX 语句中指定的。
boundary_value
为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。
boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL 表达式。boundary_value 必须与 input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与 input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。
注意：
如果 boundary_value 包含 datetime 或 smalldatetime 文字值，则为这些文字值在计算时假设 us_english 是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如 yyyymmdd 格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化 Transact-SQL 语句。若要确定服务器的语言会话，请运行 SELECT @@LANGUAGE。
...n
指定 boundary_value 提供的值的数目，不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Microsoft SQL Server 2005 数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。
LEFT | RIGHT
指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。
创建分区函数示例：
CREATE PARTITION FUNCTION PF_Left(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO
CREATE PARTITION FUNCTION PF_Right(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO
PF_Left 和 PF_Right 分区函数的区分：
分区函数 分区1 分区2 分区3
PF_Left <= 10 > 10 and <= 20 > 20
PF_Right < 10 >= 10 and < 20 >= 20
2.4. 创建分区架构（Schema）
创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。
创建分区架构的语法如下：
GOCREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]
参数：
partition_scheme_name
分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。
partition_function_name
使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name 必须已经存在于数据库中。
ALL
指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。如果指定了 ALL，则只能指定一个 file_group_name。
file_group_name | [ PRIMARY ] [ ,...n]
指定用来持有由 partition_function_name 指定的分区的文件组的名称。file_group_name 必须已经存在于数据库中。
如果指定了 [PRIMARY]，则分区将存储于主文件组中。如果指定了 ALL，则只能指定一个 file_group_name。分区分配到文件组的顺序是从分区 1 开始，按文件组在 [,...n] 中列出的顺序进行分配。在 [,...n] 中，可以多次指定同一个 file_group_name。如果 n 不足以拥有在 partition_function_name 中指定的分区数，则 CREATE PARTITION SCHEME 将失败，并返回错误。
如果 partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。如果指定了 ALL，则单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。
在 file_group_name[ 1,...n] 中指定主文件组时，必须像在 [PRIMARY] 中那样分隔 PRIMARY，因为它是关键字。
创建分区架构示例：
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
GO
2.5. 创建分区表
定义了分区函数（逻辑结构）和分区架构（物理结构）后，既可以创建分区表来利用它们。分区表定义应使用的分区架构，而分区架构又定义其使用的分区函数。要将这三者结合起来，必须指定应用于分区函数的列 。范围分区始终只映射到表中的一列。
CREATE TABLE 语法如下：
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]
示例如下：
CREATE TABLE myRangePT1
(
ID int not null,
AGE int,
PRIMARY KEY (ID)
) ON myRangePS1(myRangePF1)
GO
2.6. 创建分区索引
索引对于提高查询性能非常有效，因此，一般应该考虑应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。
默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。
在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server 不会将索引与表对齐。
在已分区的表上创建索引（分区索引）时，应该注意以下事项：
唯一索引
建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此*将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。
非唯一索引
对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。
对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。
3. 分区操作
分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。
3.1. 拆分与合并分区
通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。
注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCTION 在单个事务中影响所有这些表或索引。
ALTER PARTITION FUNCTION 语法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
SPLIT RANGE ( boundary_value )
| MERGE RANGE ( boundary_value )
} [ ; ]
参数说明：
partition_function_name
要修改的分区函数的名称。
SPLIT RANGE ( boundary_value )
在分区函数中添加一个分区。boundary_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005 数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value 所在的范围被视为是新分区。
重要提示：
文件组必须处于联机状态，并且必须由使用此分区函数的分区方案标记为 NEXT USED，以保存新分区。在 CREATE PARTITION SCHEME 语句中，将把文件组分配给分区。如果 CREATE PARTITION SCHEME 语句分配了多余的文件组（在 CREATE PARTITION FUNCTION 语句中创建的分区数少于用于保存它们的文件组），则存在未分配的文件组，分区方案将把其中的某个文件组标记为 NEXT USED。该文件组将保存新的分区。如果分区方案未将任何文件组标记为 NEXT USED，则必须使用 ALTER PARTITION SCHEME 添加一个文件组或指定一个现有文件组来保存新分区。可以指定已保存分区的文件组来保存附加分区。由于一个分区函数可以参与多个分区方案，因此所有使用分区函数（您向其中添加了分区）的分区方案都必须拥有一个 NEXT USED 文件组。否则，ALTER PARTITION FUNCTION 将失败并出现错误，该错误显示缺少 NEXT USED 文件组的一个或多个分区方案。
MERGE [ RANGE ( boundary_value) ]
删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中。boundary_value 是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL 表达式。boundary_value 必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input_parameter_type 时，将无法在隐式转换过程中被截断。sqlserver中分表和分库有什么区别
MS SQL Server：分区表、分区索引 详解
1. 分区表简介
使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。
大型表：数据量巨大的表。
访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。
分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。
注意：只能在 SQL Server Enterprise Edition 中创建分区函数。只有 SQL Server Enterprise Edition 支持分区。
2. 创建分区表或分区索引的步骤
可以分为以下步骤：
1. 确定分区列和分区数
2. 确定是否使用多个文件组
3. 创建分区函数
4. 创建分区架构（Schema）
5. 创建分区表
6. 创建分区索引
下面详细描述的创建分区表、分区索引的步骤。
2.1. 确定分区列和分区数
在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，就可以把日期作为分区列。
确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定在哪个范围。
2.2. 确定是否使用多个文件组
为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个CPU，则SQL Server 可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。
2.3. 创建分区函数
分区函数用于定义分区的边界条件，创建分区函数的语法如下：
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]
参数说明：
partition_function_name
是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。
input_parameter_type
是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。
实际列（也称为分区列）是在 CREATE TABLE 或 CREATE INDEX 语句中指定的。
boundary_value
为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。
boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL 表达式。boundary_value 必须与 input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与 input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。
注意：
如果 boundary_value 包含 datetime 或 smalldatetime 文字值，则为这些文字值在计算时假设 us_english 是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如 yyyymmdd 格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化 Transact-SQL 语句。若要确定服务器的语言会话，请运行 SELECT @@LANGUAGE。
...n
指定 boundary_value 提供的值的数目，不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Microsoft SQL Server 2005 数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。
LEFT | RIGHT
指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。
创建分区函数示例：
CREATE PARTITION FUNCTION PF_Left(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO
CREATE PARTITION FUNCTION PF_Right(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO
PF_Left 和 PF_Right 分区函数的区分：
分区函数 分区1 分区2 分区3
PF_Left <= 10 > 10 and <= 20 > 20
PF_Right < 10 >= 10 and < 20 >= 20
2.4. 创建分区架构（Schema）
创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。
创建分区架构的语法如下：
GOCREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]
参数：
partition_scheme_name
分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。
partition_function_name
使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name 必须已经存在于数据库中。
ALL
指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。如果指定了 ALL，则只能指定一个 file_group_name。
file_group_name | [ PRIMARY ] [ ,...n]
指定用来持有由 partition_function_name 指定的分区的文件组的名称。file_group_name 必须已经存在于数据库中。
如果指定了 [PRIMARY]，则分区将存储于主文件组中。如果指定了 ALL，则只能指定一个 file_group_name。分区分配到文件组的顺序是从分区 1 开始，按文件组在 [,...n] 中列出的顺序进行分配。在 [,...n] 中，可以多次指定同一个 file_group_name。如果 n 不足以拥有在 partition_function_name 中指定的分区数，则 CREATE PARTITION SCHEME 将失败，并返回错误。
如果 partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。如果指定了 ALL，则单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。
在 file_group_name[ 1,...n] 中指定主文件组时，必须像在 [PRIMARY] 中那样分隔 PRIMARY，因为它是关键字。
创建分区架构示例：
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
GO
2.5. 创建分区表
定义了分区函数（逻辑结构）和分区架构（物理结构）后，既可以创建分区表来利用它们。分区表定义应使用的分区架构，而分区架构又定义其使用的分区函数。要将这三者结合起来，必须指定应用于分区函数的列 。范围分区始终只映射到表中的一列。
CREATE TABLE 语法如下：
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]
示例如下：
CREATE TABLE myRangePT1
(
ID int not null,
AGE int,
PRIMARY KEY (ID)
) ON myRangePS1(myRangePF1)
GO
2.6. 创建分区索引
索引对于提高查询性能非常有效，因此，一般应该考虑应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。
默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。
在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server 不会将索引与表对齐。
在已分区的表上创建索引（分区索引）时，应该注意以下事项：
唯一索引
建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此*将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。
非唯一索引
对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。
对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。
3. 分区操作
分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。
3.1. 拆分与合并分区
通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。
注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCTION 在单个事务中影响所有这些表或索引。
ALTER PARTITION FUNCTION 语法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
SPLIT RANGE ( boundary_value )
| MERGE RANGE ( boundary_value )
} [ ; ]
参数说明：
partition_function_name
要修改的分区函数的名称。
SPLIT RANGE ( boundary_value )
在分区函数中添加一个分区。boundary_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005 数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value 所在的范围被视为是新分区。
重要提示：
文件组必须处于联机状态，并且必须由使用此分区函数的分区方案标记为 NEXT USED，以保存新分区。在 CREATE PARTITION SCHEME 语句中，将把文件组分配给分区。如果 CREATE PARTITION SCHEME 语句分配了多余的文件组（在 CREATE PARTITION FUNCTION 语句中创建的分区数少于用于保存它们的文件组），则存在未分配的文件组，分区方案将把其中的某个文件组标记为 NEXT USED。该文件组将保存新的分区。如果分区方案未将任何文件组标记为 NEXT USED，则必须使用 ALTER PARTITION SCHEME 添加一个文件组或指定一个现有文件组来保存新分区。可以指定已保存分区的文件组来保存附加分区。由于一个分区函数可以参与多个分区方案，因此所有使用分区函数（您向其中添加了分区）的分区方案都必须拥有一个 NEXT USED 文件组。否则，ALTER PARTITION FUNCTION 将失败并出现错误，该错误显示缺少 NEXT USED 文件组的一个或多个分区方案。
MERGE [ RANGE ( boundary_value) ]
删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中。boundary_value 是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL 表达式。boundary_value 必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input_parameter_type 时，将无法在隐式转换过程中被截断。SQL Server 自动化管理分区设计方案
一 设计说明
设计这个自动化的目的是想要交替 重复地使用固定的几个分区(分区编号 ~ )来保存数据 当最后一个分区就是快满的时候 我们会把最旧数据的分区的数据清空出分区 新数据就可以使用老分区空间了
应用这个自动化管理分区的环境是有些的 其一 分区的数据是呈现递增的 比如分区字段是自增Id值 或者是以日期作为分区;其二 可以接受 历史数据被移除分区表带来的问题 其三 一天进库的数量不应大于分区管理表PartitionManage中Part_Value与 Change_Value的差 因为我们作业执行的频率是 天 不过你可以调整Change_Value或者作业的执行频率;
具体脚本可以参考 SQL Server 自动化删除表分区设计方案
二 看图说话
(图 整体概念图)
数据流经过分区方案 被分配到不同的分区中 从图中可以看出 分区是可以重复利用的 后台有一个所谓的自动化切换分区的作业在跑 目的就是如果 重复利用这些分区 这里的PRIMARY目的就是说明它与其它文件组的一个平级关系 而且我们在做交换分区时候也会用到PRIMARY 需要事先分配足够 的空间
(图 自动化设计图)
这是自动化切换分区作业的逻辑处理 其中分区管理表的设计是比较重要的 它的灵活度关系到整个自动化的效果; 这个逻辑有以下几个特点
分区的索引进行存储位置对齐;其它索引在创建时就使用了分区方案 索引数据跟随分区数据一起存储在分区中;
分区管理表 包含了分区记录数预警设计 在Id达到这个值后就会进行交换分区;
分区管理表 FileGroup_String字段的数据可以通过SQL脚本自动化生成 条件就是分区文件组名称需要有规律;
临时表是创建在PRIMARY主分区上 跟原表使用相同的分区方案;需要事先给PRIMARY分配大于或者等于一个分区文件大小的空间 这样在交换分区的时候就不用增量为主分区分配数据空间;
交换旧数据到临时表 使用下面的语句可以把数据交换到相同的分区中编号 这样可以应对临时表就是一个历史表 而好处就是历史表也同样使用了分区
ALTER TABLE [tb] SWITCH PARTITION @PARTITION_num TO [Temp_tb] PARTITION@PARTITION_num
这里需要先修改分区方案 才能修改分区函数 这个跟创建分区函数与分区方案的顺序是刚好相反的
(图 分区管理表PartitionManage)
字段说明 Change_Value(预警Id值)Part_Value(分区函数值)FileGroup_String(分区文件组名称)IsDone(状态)UpdateTime(更新时间);
这就是那个分区管理表(PartitionManage) 它是经过了几个版本后才把字段确定下来的 现在它已经比较完善了 能应对比较多的情况
比如我们可以修改预警值(Change_Value) 让数据提早进入交换分区;
比如我们可以修改分区值(Part_Value) 达到调整分区间隔的目的;
比如我们可以修改分区文件组名称(FileGroup_String) 达到跳级文件组的目的;通过修改分区管理表来设置分区值与分区文件组的对应关系;
再比如 我们一次性修改了分区方案和分区函数 已经去到很后面的分区值了 那么我们只要设置这些分区值的状态(IsDone)为 (True)就可以解决了
记录了进行交换分区的时间(UpdateTime) 方便查询;
(图 分区为Id字段的记录分布图)
这是一个实战中的分区情况 这样的分区特点就是分区里面的记录数基本上是持平的 在Partition_num= 的记录中明显多了很多记录 这就是因为我们没有及时进行交换分区造成的
(图 分区为ClassId(分类)字段的记录分布图)
lishixin/Article/program/SQLServer/201311/22352如何对已经存在的数据表进行分区
过程中为SQL Server数据表划分为三个步骤：
1）建立分区函数
2）建立分区方案
3）对表的分区
第一步：建立分区函数
分区函数定义[U]如何[/ U]，也就是你想怎么SQL Server数据分区。这里不是一个特定的表作为一个例子，但总的技术部门总结的数据。
分区指定为每个分区用分区边界来实现。例如，假设我们有一个客户表，其中包含所有的企业客户信息，客户信息与客户的唯一标识号，客户编号从1到100万。我们可以使用下面的分区功能（这里称为customer_Partfunc）把这个表分为四个分区：
CREATE PARTITION FUNCTION customer_partfunc（INT）
AS RANGE RIGHT
输入值（ 250000，500000，750000）
这些边界划分指定了四个分区。第一个分区包含所有值？少于25万的记录。第二个分区包含所有值？之间250,000和499,999的记录。第三个分区包含所有值？500,000至749,999记录。大于或等于750,000的所有其他记录都包含在第四个分区中。
请注意，此示例使用“RANGE RIGHT”条款。这表明，该边界值是分区的右侧。同样，如果您使用“RANGE LEFT”条款，那么第一个分区将包含所有的值小于或等于25万条记录;？第二个分区将包含所有的值250001和500000之间的记录，等等？？
第二步：创建
分区方案
一旦如何分区功能分区后的数据完整的定义，下一步是创建一个分区方案，定义[ U]，其中[/ U]，这就是你要分区的数据。这是一个非常简单的过程，例如，如果我有四个文件组，从“FG1”到“FG4”的名字，那么你可以使用下面的分区方案：
创建分区计划customer_partscheme
作为间隔customer_partfunc
要（FG1，FG2，FG3，FG4）
请注意，我们现在把一个分区函数连接到分区规划，但我们还没有连接在分区方案的任何具体的数据库表。这是重复使用的功能函数时。我们可以利用这个功能来分区方案（或只是一个分区函数）对数据库表的任何数据。
第三步：表已分区
建立良好的分区方案后，就可以开始对表进行分区。这是最简单的一步，只需加上“ON”子句中的表创建语句指定表的分区方案，并申请表列的分区方案。你不需要指定分区函数，分区方案已经因为分区函数定义。
例如，假设你想使用上述的分区方案来创建一个客户表，您需要使用以下Transact-SQL语句：
CREATE TABLE客户（姓数据类型为nvarchar（40），姓氏为nvarchar（40），CUSTOMERNUMBER INT）
开customer_partscheme（CUSTOMERNUMBER）如何对已经存在的数据表进行分区
过程中为SQL Server数据表划分为三个步骤：
1）建立分区函数
2）建立分区方案
3）对表的分区
第一步：建立分区函数
分区函数定义[U]如何[/ U]，也就是你想怎么SQL Server数据分区。这里不是一个特定的表作为一个例子，但总的技术部门总结的数据。
分区指定为每个分区用分区边界来实现。例如，假设我们有一个客户表，其中包含所有的企业客户信息，客户信息与客户的唯一标识号，客户编号从1到100万。我们可以使用下面的分区功能（这里称为customer_Partfunc）把这个表分为四个分区：
CREATE PARTITION FUNCTION customer_partfunc（INT）
AS RANGE RIGHT
输入值（ 250000，500000，750000）
这些边界划分指定了四个分区。第一个分区包含所有值？少于25万的记录。第二个分区包含所有值？之间250,000和499,999的记录。第三个分区包含所有值？500,000至749,999记录。大于或等于750,000的所有其他记录都包含在第四个分区中。
请注意，此示例使用“RANGE RIGHT”条款。这表明，该边界值是分区的右侧。同样，如果您使用“RANGE LEFT”条款，那么第一个分区将包含所有的值小于或等于25万条记录;？第二个分区将包含所有的值250001和500000之间的记录，等等？？
第二步：创建
分区方案
一旦如何分区功能分区后的数据完整的定义，下一步是创建一个分区方案，定义[ U]，其中[/ U]，这就是你要分区的数据。这是一个非常简单的过程，例如，如果我有四个文件组，从“FG1”到“FG4”的名字，那么你可以使用下面的分区方案：
创建分区计划customer_partscheme
作为间隔customer_partfunc
要（FG1，FG2，FG3，FG4）
请注意，我们现在把一个分区函数连接到分区规划，但我们还没有连接在分区方案的任何具体的数据库表。这是重复使用的功能函数时。我们可以利用这个功能来分区方案（或只是一个分区函数）对数据库表的任何数据。
第三步：表已分区
建立良好的分区方案后，就可以开始对表进行分区。这是最简单的一步，只需加上“ON”子句中的表创建语句指定表的分区方案，并申请表列的分区方案。你不需要指定分区函数，分区方案已经因为分区函数定义。
例如，假设你想使用上述的分区方案来创建一个客户表，您需要使用以下Transact-SQL语句：
CREATE TABLE客户（姓数据类型为nvarchar（40），姓氏为nvarchar（40），CUSTOMERNUMBER INT）
开customer_partscheme（CUSTOMERNUMBER）如何创建SQL Server 2005表分区
创建一个分区表首先要定义你用来映射表内分区的分区函数。在下面定义的分区函数中，我将使用三个分区，每个分区对应于SalesHistoryArchive表中的每一种产品类型，这个表将在稍后定义。基本上，这些分区会把SalesHistoryArchive档案表划分成三个不同的表，它们由SQL Server自动维护。CREATE PARTITION FUNCTION [pf_Proct_Partition](VARCHAR(10)) AS RANGE LEFTFOR VALUES (N'BigScreen', N'Computer', N'PoolTable')定义函数的范围决定分区值属于哪一个边界。RNAGE LEFT：指定分区值将小于或等于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于或等于‘BigScreen’的产品将映射到第一个分区。任何名称大于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。RANGE RIGHT：指定分区值将小于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于‘BigScreen’的产品将映射到第一个分区。任何名称大于或等于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。建好分区函数后，现在我需要建立分区方案。我将把例子中的所有分区都映射到主文件组中。如果我希望将分区映射到不同的文件组，我会按文件组列表的顺序加入文件组名称。下面是创建分区方案的代码：CREATE PARTITION SCHEME [ps_Proct_Scheme] AS PARTITION[pf_Proct_Partition]ALL TO ([PRIMARY])现在我建立需要分区的表，我可以创建SalesHistoryArchive表并在其中加载数据。在CREATE TABLE语句末尾，分区方案使用表中的一个字段名告诉SQL Server如何映射需要分区的表中的数据。查看列表A中的代码样本。我需要对数据进行一些查询，保证分区正常运行。下面的查询返回SalesHistoryArchive表的所有行，并使用$partition函数指出返回的行属于哪个分区：SELECT $partition.[pf_Proct_Partition](Proct), *FROM SalesHistoryArchive这个查询返回所有映射到SalesHistoryArchive表中的分区：SELECT * From sys.partitionsWHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'接下来…在后面的文章中，我将解释如何修改表中的这些分区，从而在其中增加新数据，并非常高效地删除旧数据。
如何创建SQL Server 2005表分区
创建一个分区表首先要定义你用来映射表内分区的分区函数。在下面定义的分区函数中，我将使用三个分区，每个分区对应于SalesHistoryArchive表中的每一种产品类型，这个表将在稍后定义。基本上，这些分区会把SalesHistoryArchive档案表划分成三个不同的表，它们由SQL Server自动维护。CREATE PARTITION FUNCTION [pf_Proct_Partition](VARCHAR(10)) AS RANGE LEFTFOR VALUES (N'BigScreen', N'Computer', N'PoolTable')定义函数的范围决定分区值属于哪一个边界。RNAGE LEFT：指定分区值将小于或等于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于或等于‘BigScreen’的产品将映射到第一个分区。任何名称大于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。RANGE RIGHT：指定分区值将小于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于‘BigScreen’的产品将映射到第一个分区。任何名称大于或等于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。建好分区函数后，现在我需要建立分区方案。我将把例子中的所有分区都映射到主文件组中。如果我希望将分区映射到不同的文件组，我会按文件组列表的顺序加入文件组名称。下面是创建分区方案的代码：CREATE PARTITION SCHEME [ps_Proct_Scheme] AS PARTITION[pf_Proct_Partition]ALL TO ([PRIMARY])现在我建立需要分区的表，我可以创建SalesHistoryArchive表并在其中加载数据。在CREATE TABLE语句末尾，分区方案使用表中的一个字段名告诉SQL Server如何映射需要分区的表中的数据。查看列表A中的代码样本。我需要对数据进行一些查询，保证分区正常运行。下面的查询返回SalesHistoryArchive表的所有行，并使用$partition函数指出返回的行属于哪个分区：SELECT $partition.[pf_Proct_Partition](Proct), *FROM SalesHistoryArchive这个查询返回所有映射到SalesHistoryArchive表中的分区：SELECT * From sys.partitionsWHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'接下来…在后面的文章中，我将解释如何修改表中的这些分区，从而在其中增加新数据，并非常高效地删除旧数据。
sqlserver2008怎么实现自动分区表
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组USE [master]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]GO-- 创建和文件组相对应的文件，由于只有3个盘USE [master]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]GO
注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看SQL Server表分区。
2.建立相应的分区函数和分区方案
USE TClientLog;CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)AS RANGE right FOR VALUES (‘2015-08-01 00:00:00‘,‘2015-08-02 00:00:00‘,‘2015-08-03 00:00:00‘,‘2015-08-04 00:00:00‘,‘2015-08-05 00:00:00‘,‘2015-08-06 00:00:00‘,‘2015-08-07 00:00:00‘,‘2015-08-08 00:00:00‘,‘2015-08-09 00:00:00‘,‘2015-08-10 00:00:00‘,‘2015-08-11 00:00:00‘,‘2015-08-12 00:00:00‘,‘2015-08-13 00:00:00‘,‘2015-08-14 00:00:00‘,‘2015-08-15 00:00:00‘,‘2015-08-16 00:00:00‘,‘2015-08-17 00:00:00‘,‘2015-08-18 00:00:00‘,‘2015-08-19 00:00:00‘,‘2015-08-20 00:00:00‘,‘2015-08-21 00:00:00‘,‘2015-08-22 00:00:00‘,‘2015-08-23 00:00:00‘,‘2015-08-24 00:00:00‘,‘2015-08-25 00:00:00‘,‘2015-08-26 00:00:00‘,‘2015-08-27 00:00:00‘,‘2015-08-28 00:00:00‘,‘2015-08-29 00:00:00‘,‘2015-08-30 00:00:00‘,‘2015-08-31 00:00:00‘);CREATE PARTITION SCHEME part_day_rang_schemeAS PARTITION part_day_rang_funcTO (FGDAY00,FGDAY01,FGDAY02,FGDAY03,FGDAY04,FGDAY05,FGDAY06,FGDAY07,FGDAY08,FGDAY09,FGDAY10,FGDAY11,FGDAY12,FGDAY13,FGDAY14,FGDAY15,FGDAY16,FGDAY17,FGDAY18,FGDAY19,FGDAY20,FGDAY21,FGDAY22,FGDAY23,FGDAY24,FGDAY25,FGDAY26,FGDAY27,FGDAY28,FGDAY29,FGDAY30,FGDAY31);
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog]( [SynID] [nchar](38) NOT NULL, [ParkingId] [int] NOT NULL, [ParkingBoxId] [int] NOT NULL, [Message] [varchar](max) NULL, [OccurTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [ErrorLevel] [int] NOT NULL, [State] [int] NULL, [IsSend] [int] NULL, CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED ( [SynID] ASC, [OccurTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])) ON [part_day_rang_scheme]([OccurTime])GOCREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] ( [ParkingId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])GO
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================-- Author: zhangkun-- Create date: <2015.08.07>-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>-- =============================================-- 1.修改分区方案和分区函数-- 2.进行分区交换，将归档数据放入历史表alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIMEAS BEGIN DECLARE @flag CHAR(1) --标志位 IF @td IS NULL --如果@td为null，则默认当天 SET @td = GETDATE()-- 1.修改分区方案和分区函数,当天新增后数第七天的日期 BEGIN DECLARE @td_next7 DATETIME DECLARE @day_next7 VARCHAR(2) DECLARE @sql NVARCHAR(MAX) --动态sql字符串 SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期 SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1 THEN ‘0‘ + DATENAME(DAY, @td_next7) ELSE DATENAME(DAY, @td_next7) END; --7天后是当月第几天SELECT @flag = COUNT(1) FROM sys.partition_functions a , sys.partition_range_values b WHERE a.name = ‘part_day_rang_func‘ AND a.function_id = b.function_id AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120) + ‘ 00:00:00.000‘; PRINT @flag; IF ( @flag != ‘1‘ ) BEGIN SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘ + @day_next7 + ‘; alter partition function part_day_rang_func() split range(‘‘‘ + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ EXEC sp_executesql @sql; END END -- 2.进行分区交换，将归档数据放入历史表 DECLARE @td_before7 DATETIME DECLARE @day_before7 VARCHAR(2) SET @td_before7 = DATEADD(DAY, -7sqlserver2008怎么实现自动分区表
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组USE [master]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]GO-- 创建和文件组相对应的文件，由于只有3个盘USE [master]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]GOALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]GO
注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看SQL Server表分区。
2.建立相应的分区函数和分区方案
USE TClientLog;CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)AS RANGE right FOR VALUES (‘2015-08-01 00:00:00‘,‘2015-08-02 00:00:00‘,‘2015-08-03 00:00:00‘,‘2015-08-04 00:00:00‘,‘2015-08-05 00:00:00‘,‘2015-08-06 00:00:00‘,‘2015-08-07 00:00:00‘,‘2015-08-08 00:00:00‘,‘2015-08-09 00:00:00‘,‘2015-08-10 00:00:00‘,‘2015-08-11 00:00:00‘,‘2015-08-12 00:00:00‘,‘2015-08-13 00:00:00‘,‘2015-08-14 00:00:00‘,‘2015-08-15 00:00:00‘,‘2015-08-16 00:00:00‘,‘2015-08-17 00:00:00‘,‘2015-08-18 00:00:00‘,‘2015-08-19 00:00:00‘,‘2015-08-20 00:00:00‘,‘2015-08-21 00:00:00‘,‘2015-08-22 00:00:00‘,‘2015-08-23 00:00:00‘,‘2015-08-24 00:00:00‘,‘2015-08-25 00:00:00‘,‘2015-08-26 00:00:00‘,‘2015-08-27 00:00:00‘,‘2015-08-28 00:00:00‘,‘2015-08-29 00:00:00‘,‘2015-08-30 00:00:00‘,‘2015-08-31 00:00:00‘);CREATE PARTITION SCHEME part_day_rang_schemeAS PARTITION part_day_rang_funcTO (FGDAY00,FGDAY01,FGDAY02,FGDAY03,FGDAY04,FGDAY05,FGDAY06,FGDAY07,FGDAY08,FGDAY09,FGDAY10,FGDAY11,FGDAY12,FGDAY13,FGDAY14,FGDAY15,FGDAY16,FGDAY17,FGDAY18,FGDAY19,FGDAY20,FGDAY21,FGDAY22,FGDAY23,FGDAY24,FGDAY25,FGDAY26,FGDAY27,FGDAY28,FGDAY29,FGDAY30,FGDAY31);
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog]( [SynID] [nchar](38) NOT NULL, [ParkingId] [int] NOT NULL, [ParkingBoxId] [int] NOT NULL, [Message] [varchar](max) NULL, [OccurTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [ErrorLevel] [int] NOT NULL, [State] [int] NULL, [IsSend] [int] NULL, CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED ( [SynID] ASC, [OccurTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])) ON [part_day_rang_scheme]([OccurTime])GOCREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] ( [ParkingId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])GO
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================-- Author: zhangkun-- Create date: <2015.08.07>-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>-- =============================================-- 1.修改分区方案和分区函数-- 2.进行分区交换，将归档数据放入历史表alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIMEAS BEGIN DECLARE @flag CHAR(1) --标志位 IF @td IS NULL --如果@td为null，则默认当天 SET @td = GETDATE()-- 1.修改分区方案和分区函数,当天新增后数第七天的日期 BEGIN DECLARE @td_next7 DATETIME DECLARE @day_next7 VARCHAR(2) DECLARE @sql NVARCHAR(MAX) --动态sql字符串 SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期 SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1 THEN ‘0‘ + DATENAME(DAY, @td_next7) ELSE DATENAME(DAY, @td_next7) END; --7天后是当月第几天SELECT @flag = COUNT(1) FROM sys.partition_functions a , sys.partition_range_values b WHERE a.name = ‘part_day_rang_func‘ AND a.function_id = b.function_id AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120) + ‘ 00:00:00.000‘; PRINT @flag; IF ( @flag != ‘1‘ ) BEGIN SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘ + @day_next7 + ‘; alter partition function part_day_rang_func() split range(‘‘‘ + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ EXEC sp_executesql @sql; END END -- 2.进行分区交换，将归档数据放入历史表 DECLARE @td_before7 DATETIME DECLARE @day_before7 VARCHAR(2) SET @td_before7 = DATEADD(DAY, -7sql server 查看分区表的分区字段如果表 PartitionTable
已分区，以下查询将返回一个或多个行。
如果表未分区，则不返回任何行。
SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
AND t.name = 'PartitionTable';
以下查询返回表的分区列的名称。
PartitionTable.
SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'PartitionTable'
AND i.type <= 1
AND c.column_id = 1;sql server 查看分区表的分区字段
如果表 PartitionTable
已分区，以下查询将返回一个或多个行。
如果表未分区，则不返回任何行。
SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
AND t.name = 'PartitionTable';
以下查询返回表的分区列的名称。
PartitionTable.
SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'PartitionTable'
AND i.type <= 1
AND c.column_id = 1;