SQL Server 动态添加分区 sql server 分区函数

转载

laokugonggao 2024-01-26 07:20:39

文章标签 SQL Server 动态添加分区 sql 创建文件数据库 文章分类 SQL Server 数据库

1.创建数据库 [UserDB]

2.添加文件组到数据库

ALTER DATABASE UserDB ADD FILEGROUP SecondFG;ALTER DATABASE UserDB ADD FILE(NAME='f2',FILENAME='D:/Microsoft SQL Server/Data/userdb2.ndf') TO FILEGROUP SecondFG

如果不想使用多文件组，可以不用加文件组，全部使用[primary]文件组。

3.创建分区函数,分区函数是属于数据库的

CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (30,40)

以上创建了一个名为 AgePF 的分区函数，输入参数数据类型为 int,函数的分隔点分别是 30 和 40。
用的 LEFT 关键字，指示分界值存在于他左边的分区上。分界值可以有 999 个，即最多支持 1000 个分区。

4.创建分区方案

CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [SecondFG],[PRIMARY])

以上使用分区函数 TempPF 创建了一个名为 AgePS 的分区方案，一个分区函数可以创建多个分区方案。
分区方案中指定第一个分区使用 [Primary] 文件组存储数据，第二个分区使用 SecondFG 存储数据，第三个分区使用 [Primary] 文件组存储数据。

5.创建基于分区方案的数据表

CREATE TABLE [UserInfo]([id] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](20) NOT NULL,[Sex] [bit] NOT NULL,[Age] [int] NOT NULL,[Tel] [varchar](32) NULL,[Email] [nvarchar](50) NOT NULL) ON [AgePS]([Age])

以上使用分区方案 AgePS 以 [Age] 列为分区列创建了一个数据表。
创建完数据表就可以插入数据了。

6.查看每分区的记录数

SELECT COUNT(*) AS 数量,$PARTITION.AgePF(Age) AS 分区 FROM UserInfo GROUP BY $PARTITION.AgePF(Age)

7.查看记录所在分区

SELECT AGE,$PARTITION.AgePF(Age) AS 分区 FROM UserInfo

8.修改分区方案

其实修改分区方案与修改分区函数一般同时执行。当增加分区时要指定分区方案中新分区的文件组，按如下语句：

ALTER PARTITION SCHEME [AgePS] NEXT USED [primary]

上句指定了当拆分出新分区时，使用 [primary] 文件组，当然这个可以随意指定数据库包括的文件组，也可以再新加入一个文件组来用。

9.修改分区函数：增加分区

ALTER PARTITION FUNCTION AgePF() SPLIT RANGE(20)

在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:
CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 30, 40)CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [PRIMARY], [SecondFG], [PRIMARY])

或在有数据的情况下用6、7两步里的脚本查看。

以上增加了一个分区，即加了一个分界值，分界值所在的分区为新分区(使用 next used 指定的文件组)，每次执行增加分区的操作都要在基于此分区函数的分区方案上执行 next used 的操作。

10.修改分区函数:合并分区

ALTER PARTITION FUNCTION AgePF() MERGE RANGE(30)

以上删除分界值 30 ，包含 30 值的分区上的数据将被转移到 30 值相邻的分区上，然后在分区方案中删除包含30 值这个分区所对应的文件组。
在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:
CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [SecondFG], [PRIMARY])CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 40)

或在有数据的情况下用6、7两步里的脚本查看记录所在分区或每分区的记录数量。

---------------------------------------------------------------------------------------

---生成分区脚本
DECLARE @DataBaseName NVARCHAR(50)--数据库名称
DECLARE @TableName NVARCHAR(50)--表名称
DECLARE @ColumnName NVARCHAR(50)--字段名称
DECLARE @PartNumber INT--需要分多少个区
DECLARE @Location NVARCHAR(50)--保存分区文件的路径
DECLARE @Size NVARCHAR(50)--分区初始化大小
DECLARE @FileGrowth NVARCHAR(50)--分区文件增量
DECLARE @FunValue INT--分区分段值
DECLARE @i INT
DECLARE @PartNumberStr NVARCHAR(50)
DECLARE @sql NVARCHAR(max)

--设置下面变量
SET @DataBaseName = 'MyDataBase'
SET @TableName = 'User'
SET @ColumnName = 'Id'
SET @PartNumber = 4
SET @Location = 'E:\DataBase\'
SET @Size = '30MB'
SET @FileGrowth = '10%'
SET @FunValue = 10000000

--1.创建文件组
SET @i = 1
PRINT '--1.创建文件组'
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)
    SET @sql = 'ALTER DATABASE ['+@DataBaseName +'] ADD FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']'
    PRINT @sql --+ CHAR(13)
    SET @i=@i+1
END

--2.创建文件
SET @i = 1
PRINT CHAR(13)+'--2.创建文件'
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)
    SET @sql = 'ALTER DATABASE ['+@DataBaseName +']
ADD FILE
(NAME = = = , FILEGROWTH = )
TO FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];'
    PRINT @sql + CHAR(13)
    SET @i=@i+1
END

--3.创建分区函数
PRINT CHAR(13)+'--3.创建分区函数'
DECLARE @FunValueStr NVARCHAR(MAX)
SET @i = 1
SET @FunValueStr = ''
WHILE @i < @PartNumber
BEGIN
    SET @FunValueStr = @FunValueStr + convert(NVARCHAR(50),(@i*@FunValue)) + ','
    SET @i=@i+1
END

SET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1)
SET @sql = 'CREATE PARTITION FUNCTION
) AS
RANGE RIGHT
FOR VALUES(
PRINT @sql + CHAR(13)

--4.创建分区方案
PRINT CHAR(13)+'--4.创建分区方案'
DECLARE @FileGroupStr NVARCHAR(MAX)
SET @i = 1
SET @FileGroupStr = ''
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)
    SET @FileGroupStr = @FileGroupStr +'[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],'
    SET @i=@i+1
END

SET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1)
SET @sql = 'CREATE PARTITION SCHEME
AS
PARTITION
TO(

PRINT @sql + CHAR(13)

--5.分区函数的记录数
PRINT CHAR(13)+'--5.分区函数的记录数'
SET @sql = 'SELECT ) AS Partition_num,
MIN() AS Min_value,MAX() AS Max_value,COUNT(1) AS Record_num
FROM
GROUP BY )
ORDER BY
PRINT @sql + CHAR(13)