SQL Server 表分区备忘

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1.创建的代码如下：

CREATE PARTITION FUNCTION [01_SubjectiveScoreInfoPartitionFunction](NVARCHAR(26))
AS RANGE LEFT FOR VALUES
(
N'',
N'',
N'',...
)CREATE PARTITION SCHEME [01_SubjectiveScoreInfoPartitionSchema]
AS PARTITION [01_SubjectiveScoreInfoPartitionFunction] ALL TO ([PRIMARY])CREATE CLUSTERED INDEX [IX_01_SubjectiveScoreInfo_Clustered] ON [01_SubjectiveScoreInfo] (QuestionGroupCode)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [01_SubjectiveScoreInfoPartitionSchema](QuestionGroupCode)

2.增加分区值的代码如下：

ALTER PARTITION FUNCTION [01_SubjectiveScoreInfoPartitionFunction] ()
SPLIT RANGE ('');

3.查询当前分区情况：

SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
WHERE pf.name = '01_SubjectiveScoreInfoPartitionFunction'

4.创建单一文件组分区完整示例代码如下：

CREATE PROCEDURE [dbo].[spPartitionSubjectiveScoreInfo]
(
@CourseID AS NVARCHAR(2)
)
AS
BEGIN
BEGIN TRAN
BEGIN TRY
DECLARE @sql NVARCHAR(MAX)
DECLARE @QuestionGroupCodeList NVARCHAR(MAX)
SET @QuestionGroupCodeList = ''
SET @sql = N'
SELECT @QuestionGroupCodeList = @QuestionGroupCodeList + ''N''''''+[Code]+'''''',''
FROM [OMSDB].[dbo].[QuestionGroupInfo]
WHERE LEFT(RIGHT(Code,8),2)=@CourseID'
EXECUTE sp_executesql @sql,N'@QuestionGroupCodeList NVARCHAR(MAX) OUTPUT,@CourseID NVARCHAR(2)',
@QuestionGroupCodeList=@QuestionGroupCodeList OUTPUT,
@CourseID=@CourseID IF RIGHT(@QuestionGroupCodeList,1)=','
SET @QuestionGroupCodeList = LEFT(@QuestionGroupCodeList,LEN(@QuestionGroupCodeList)-1) SET @sql = N'
CREATE PARTITION FUNCTION ['+@CourseID+'_SubjectiveScoreInfoPartitionFunction](NVARCHAR(26))
AS RANGE LEFT FOR VALUES ( '+ @QuestionGroupCodeList +' )'
EXECUTE sp_executesql @sql SET @sql = N'
CREATE PARTITION SCHEME ['+@CourseID+'_SubjectiveScoreInfoPartitionSchema]
AS PARTITION ['+@CourseID+'_SubjectiveScoreInfoPartitionFunction] ALL TO ([PRIMARY])'
EXECUTE sp_executesql @sql SET @sql = N'
CREATE CLUSTERED INDEX [IX_'+@CourseID+'_SubjectiveScoreInfo_Clustered] ON ['+@CourseID+'_SubjectiveScoreInfo] (QuestionGroupCode)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON ['+@CourseID+'_SubjectiveScoreInfoPartitionSchema](QuestionGroupCode)'
EXECUTE sp_executesql @sql
COMMIT TRAN END TRY
BEGIN CATCH
PRINT 'Error:' + CONVERT(VARCHAR,ERROR_NUMBER()) + ',Message:' + error_message()
ROLLBACK TRAN
END CATCH
END

5.创建多文件组分区完整代码如下：

USE [OMSDB]DECLARE @dbPath NVARCHAR(MAX)
DECLARE @CourseID NVARCHAR(MAX)
SET @dbPath = 'D:\firstelite\data2'
SET @CourseID = ''DECLARE @sql NVARCHAR(MAX)
DECLARE @QuestionGroupCodes TABLE(QuestionGroupCode NVARCHAR(50))
DECLARE @QuestionGroupCodeList NVARCHAR(MAX)INSERT INTO @QuestionGroupCodes(QuestionGroupCode)
SELECT [Code] FROM [OMSDB].[dbo].[QuestionGroupInfo]
WHERE LEFT(RIGHT(Code,8),2)=@CourseID
ORDER BY [Code] ASC--删除所有文件组
SET @sql = N''
SELECT @sql = @sql + 'DBCC SHRINKFILE(N'''+QuestionGroupCode+''', EMPTYFILE);' + CHAR(13) + CHAR(10)
+ 'ALTER DATABASE OMSDB REMOVE FILE ['+QuestionGroupCode+'];' + CHAR(13) + CHAR(10)
+ 'ALTER DATABASE OMSDB REMOVE FILEGROUP FG' + QuestionGroupCode + ';' + CHAR(13) + CHAR(10)
FROM @QuestionGroupCodes
PRINT @sql
EXECUTE sp_executesql @sql--动态创建所有文件组
SET @sql = N''
SELECT @sql = @sql + 'ALTER DATABASE OMSDB ADD FILEGROUP FG' + QuestionGroupCode + ';' + CHAR(13) + CHAR(10)
+ 'ALTER DATABASE OMSDB ADD FILE(name=N'''+ QuestionGroupCode +''',filename=N'''+ @dbPath + '\' + QuestionGroupCode +'.ndf'',size=5MB,filegrowth=5MB) to filegroup FG' + QuestionGroupCode + ';' + CHAR(13) + CHAR(10)
FROM @QuestionGroupCodes
PRINT @sql
EXECUTE sp_executesql @sql--动态创建分区函数
SET @QuestionGroupCodeList = ''
SELECT @QuestionGroupCodeList = @QuestionGroupCodeList + 'N'''+[QuestionGroupCode]+''','
FROM @QuestionGroupCodesIF RIGHT(@QuestionGroupCodeList,1)=','
SET @QuestionGroupCodeList = LEFT(@QuestionGroupCodeList,LEN(@QuestionGroupCodeList)-1)SET @sql = N'
CREATE PARTITION FUNCTION ['+@CourseID+'_SubjectiveScoreInfoPartitionFunction](NVARCHAR(26))
AS RANGE LEFT FOR VALUES ( '+ @QuestionGroupCodeList +' )'
PRINT @sql
EXECUTE sp_executesql @sql--动态创建分区模式
SET @QuestionGroupCodeList = '[PRIMARY],'
SELECT @QuestionGroupCodeList = @QuestionGroupCodeList + '[FG'+[QuestionGroupCode]+'],'
FROM @QuestionGroupCodesIF RIGHT(@QuestionGroupCodeList,1)=','
SET @QuestionGroupCodeList = LEFT(@QuestionGroupCodeList,LEN(@QuestionGroupCodeList)-1)SET @sql = N'
CREATE PARTITION SCHEME ['+@CourseID+'_SubjectiveScoreInfoPartitionSchema]
AS PARTITION ['+@CourseID+'_SubjectiveScoreInfoPartitionFunction] TO (' + @QuestionGroupCodeList + ')'
PRINT @sql
EXECUTE sp_executesql @sql--创建表分区
SET @sql = N'
CREATE CLUSTERED INDEX [IX_'+@CourseID+'_SubjectiveScoreInfo_Clustered] ON ['+@CourseID+'_SubjectiveScoreInfo]
(
QuestionGroupCode,
ID
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON ['+@CourseID+'_SubjectiveScoreInfoPartitionSchema](QuestionGroupCode)'
PRINT @sql
EXECUTE sp_executesql @sql--SET @sql = N'
-- CREATE CLUSTERED INDEX [IX_'+@CourseID+'_SubjectiveScoreInfoFlag_Clustered] ON ['+@CourseID+'_SubjectiveScoreInfoFlag]
-- (
-- [QuestionGroupCode]
-- ) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-- ON ['+@CourseID+'_SubjectiveScoreInfoPartitionSchema](QuestionGroupCode)'
--PRINT @sql
--EXECUTE sp_executesql @sql--其它相关语句
--DROP PARTITION SCHEME [01_SubjectiveScoreInfoPartitionSchema]
--DROP PARTITION FUNCTION [01_SubjectiveScoreInfoPartitionFunction]--ALTER DATABASE OMSDB ADD FILEGROUP FG11111111111500040401000301;
--ALTER DATABASE OMSDB ADD FILE(name=N'11111111111500040401000301',filename=N'D:\firstelite\data2\11111111111500040401000301.ndf',size=5MB,filegrowth=5MB);--DBCC SHRINKFILE(N'11111111111500040401000301', EMPTYFILE)
--ALTER DATABASE OMSDB REMOVE FILE [11111111111500040401000301]
--ALTER DATABASE OMSDB REMOVE FILEGROUP FG11111111111500040401000301;

6.MSDN示例代码如下：

A. 将已分区表或索引的一个分区拆分为两个分区

以下示例创建了一个分区函数，以将表或索引划分为四个分区。ALTER PARTITION FUNCTION 将某个分区拆分为两个分区，从而总共创建了五个分区。

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
--Split the partition between boundary_values 100 and 1000
--to create two partitions between boundary_values 100 and 500
--and between boundary_values 500 and 1000.ALTER PARTITION SCHEME SchemaForParirion NEXT USED 'PRIMARY'
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);

B. 将已分区表的两个分区合并为一个分区

以下示例与上例创建同一分区函数，然后将两个分区合并为一个分区，从而总共创建了三个分区。

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
--Merge the partitions between boundary_values 1 and 100
--and between boundary_values 100 and 1000 to create one partition
--between boundary_values 1 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
MERGE RANGE (100);

总结：

目前无法做如何删除分区，只能更改分区值，所以分区结构一旦创建，无法更改。

网上有人给出如下2种方案：

1.新建一张表，把数据插入新表，删除旧表，才能删除分区，然后重命名旧表，新建表分区。一般分区的表数据量都特别大，迁移数据耗时，本人没有试验。

2.按如下步骤，但我试了，没有成功，最后2步一直提示分区使用中，无法操作。

假设你创建的步骤是这样的：
1、CREATE PARTITION FUNCTION PF1(INT) AS RANGE LEFT FOR VALUES (1, 2, 3, 4)
2、CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY])
3、CREATE CLUSTERED INDEX CIDX_X ON X(col1) ON PS1(col1)
删除的步骤应该是这样的：
1、DROP INDEX CIDX_X on X
2、CREATE CLUSTERED INDEX CIDX_X1 ON X(col1) ON [PRIMARY]
3、DROP PARTITION SCHEME PS1
4、DROP PARTITION FUNCTION PF1
5、DROP INDEX CIDX_X1 ON X