[转载]SqlServer 表分区以及自动化分区 - tyb1222 - 博客园

[转载]SqlServer 表分区以及自动化分区 – tyb1222 – 博客园.

本文是我关于数据库分区的方案的一些想法,或许有些问题。仅供大家讨论。SQLServer (SQLServer 2005\SQLServer 2008)实现分区需要在企业版下进行.

SQLServer的分区分为大致有以下个过程:1、创建文件组用以存放数据文件 2、创建文件组用户数据文件 3、创建分区函数 4、创建分区方案  5、在分区方案下创建表

本文是在SQLServer2012 下完成的。

过程:

1、新建数据库,在属性中创建文件以及文件组。如下图:

可以在下图中选择文件组、或者新建文件组用户存放上图中新建的文件:

2、创建分区函数

CREATE PARTITION FUNCTION [partitionById](int) 
AS RANGE LEFT FOR VALUES (100, 200, 300)

3、创建分区方案

CREATE PARTITION SCHEME [partitionSchemeById] 
AS PARTITION [partitionById] --分区函数
TO ([FileGroup1], [FileGroup2],  [FileGroup3],[FileGroup4])

注意以上分区函数使用的是LEFT ,根据后面的值指明了数据库中如何存放。以上存放方式为:-∞,100],(100,200],(200,300],(300,+∞).此分区方案是依据分区函数

partitionById 创建的。那就是说以上Id的存储区间分别被放在[FileGroup1], [FileGroup2],  [FileGroup3],[FileGroup4]文件组的文件中。

4、依据分区方案创建表

复制代码
CREATE TABLE [dbo].[Account](
    [Id] [int] NULL,
    [Name] [varchar](20) NULL,
    [Password] [varchar](20) NULL,
    [CreateTime] [datetime] NULL
) ON partitionSchemeById(Id)
复制代码

注意:创建表的脚本中需要指明分区方案和分区依据列

查看某分区的数据:

SELECT * FROM 
[dbo].[Account]
WHERE $PARTITION.[partitionById](Id)=1

查询结果如下图:

 

至此,分区似乎已经结束了。但是看看后一个分区里的数据:Id>=400的全部放在了一个数据文件中。这样在有可能瓶颈就发生在了这个分区中。

如果数据不停的增长,希望分区也不断的自动增加。如:每天生成一个新的分区来存放分区新的数据。如到第二天时,新生成一个分区来存放(400,500 ]的数据。

这里我采用了SQL Job的方式来自动产生分区:

 

复制代码
DECLARE @maxValue INT,
    @secondMaxValue INT,
    @differ    INT,
    @fileGroupName VARCHAR(200),
    @fileNamePath    VARCHAR(200),
    @fileName   VARCHAR(200),
    @sql        NVARCHAR(1000)

SET @fileGroupName='FileGroup'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') 
PRINT @fileGroupName
SET @sql='ALTER DATABASE [Test] ADD FILEGROUP '+@fileGroupName
PRINT @sql
EXEC(@sql)

SET @fileNamePath='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLINSTANCE\MSSQL\DATA\'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') +'.NDF'
SET @fileName=N'File'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') 

SET @sql='ALTER DATABASE [Test] ADD FILE (NAME='''+@fileName+''',FILENAME=N'''+@fileNamePath+''') TO FILEGROUP'+'    '+@fileGroupName
PRINT @sql
PRINT 1
EXEC(@sql)
PRINT 2

--修改分区方案,用一个新的文件组用于存放下一新增的数据
SET @sql='ALTER PARTITION SCHEME [partitionSchemeById] NEXT USED'+'    '+@fileGroupName
EXEC(@sql)
  --分区架构
PRINT 3 
SELECT @maxValue =CONVERT(INT,MAX(value))
FROM SYS.PARTITION_RANGE_VALUES PRV

SELECT @secondMaxValue = CONVERT(INT,MIN(value))
FROM 
(
    SELECT TOP 2 * FROM SYS.PARTITION_RANGE_VALUES ORDER BY VALUE DESC
)
 PRV 

SET @differ=@maxValue - @secondMaxValue 

ALTER PARTITION FUNCTION partitionById()  --分区函数
SPLIT RANGE (@maxValue+@differ)
复制代码

这样在计划里指定每天什么时候运行,下图:

 

参考:http://www.cnblogs.com/lyhabc/articles/2623685.html

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏