一.分区脚本
--生成分区脚本
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 = N''FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data'',FILENAME = N'''+@Location+'FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data.ndf'',SIZE = '+@Size+', FILEGROWTH = '+@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
Fun_'+@TableName+'_'+@ColumnName+'(INT) AS
RANGE RIGHT
FOR VALUES('+@FunValueStr+')'
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
Sch_'+@TableName+'_'+@ColumnName+' AS
PARTITION Fun_'+@TableName+'_'+@ColumnName+'
TO('+@FileGroupStr+')'
PRINT @sql + CHAR(13)
--5.分区函数的记录数
PRINT CHAR(13)+'--5.分区函数的记录数'
SET @sql = 'SELECT $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+') AS Partition_num,
MIN('+@ColumnName+') AS Min_value,MAX('+@ColumnName+') AS Max_value,COUNT(1) AS Record_num
FROM dbo.'+@TableName+'
GROUP BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+')
ORDER BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+');'
PRINT @sql + CHAR(13)
生成分区脚本
二.脚本运行结果
--1.创建文件组
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_01]
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_02]
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_03]
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_04]
--2.创建文件
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_01_data',FILENAME = N'E:\DataBase\FG_User_Id_01_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_01];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_02_data',FILENAME = N'E:\DataBase\FG_User_Id_02_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_02];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_03_data',FILENAME = N'E:\DataBase\FG_User_Id_03_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_03];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_04_data',FILENAME = N'E:\DataBase\FG_User_Id_04_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_04];
--3.创建分区函数
CREATE PARTITION FUNCTION
Fun_User_Id(INT) AS
RANGE RIGHT
FOR VALUES(10000000,20000000,30000000)
--4.创建分区方案
CREATE PARTITION SCHEME
Sch_User_Id AS
PARTITION Fun_User_Id
TO([FG_User_Id_01],[FG_User_Id_02],[FG_User_Id_03],[FG_User_Id_04])
--5.分区函数的记录数
SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num,
MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
FROM dbo.User
GROUP BY $PARTITION.Fun_User_Id(Id)
ORDER BY $PARTITION.Fun_User_Id(Id);
结果
按时间分区创建分区函数
create partition function [PF_GK] (datetime) as range left for values
('2019-01-01','2019-02-01','2019-03-01','2019-04-01')
三.新建表应用分区方案
--如果要在新建表上应用分区方案则如下
--应用分区计划到新建表
create table test3 -- 表名称
(
id int not null primary key nonclustered, -- 字段名称、字段类型、是否可空、主键约束、非聚集索引
column1 int not null
) ON [schema_partiton](id)
如果新建表只是修改了主键为非聚集索引,则需要新建分区索引
--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder]
(
[OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId]) --分区方案(列名)
四.已有表构建
将普通表转换为分区表: 当数据库已经存在数据的时候,就不能像上面那样直接建立分区表了,只能将普通表转换为分区表,只需在该普通表上创建一个聚集索引,并在该聚集索引中使用分区方案即可。
如果是已经存在的聚集索引,那么需要删除然后重新建立,并使用分区方案。
现在我有一个现成的表 UserInfo,因为它存在一个主键,而建立主键时,系统会自动为主键列添加聚集索引,因为这个聚集索引没法删除。
所以我现在要先删除这个主键,然后重新建立一个主键,并设置为非聚集索引,然后为主键创建一个聚集索引(会覆盖非聚集索引),并使用分区方案指定分区列即可。
-- 根据 指定表名 查询 表的约束
exec sp_helpconstraint UserInfo -- UserInfo 表名
-- 根据指定主键约束名删除指定表的主键约束
alter table UserInfo drop constraint PK__UserInfo__5A2040BBA6D6767A
-- 添加主键约束,但设置为非聚集索引
alter table UserInfo add constraint PK__UserInfo__5A2040BBA6D6767A primary key nonclustered (U_Id)
-- 添加一个聚集索引,并使用分区方案指定分区的列
create clustered index CLU_StuNo -- 索引名称
on UserInfo(U_Id) -- 指定添加索引的表(添加索引的列)
on Part_Plan(U_Id) -- 分区方案名称(分区依据的列)
五.查看数据库分区信息
如果表 A已分区,以下查询将返回一个或多个行。 如果表未分区,则不返回任何行。
SELECT *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] IN (0,1)
JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = 'A';
GO
A的分区列的名称。
SELECT
t.[object_id] AS ObjectID
, 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]
AND i.[type] <= 1 -- clustered index or a heap
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 >= 1 -- because 0 = non-partitioning column
JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
WHERE t.name = 'A' ;
GO
A表中的每个分区返回边界值。
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'A' AND i.type <= 1
ORDER BY p.partition_number;
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
ObjectName,
IndexID,
PartitionNumber
数据库分区信息
六.分区表转普通表
1、删除分区索引(你可以直接在SQL Server Management Studio上将分区索引删除,也可以使用SQL语句删除,如本例中可以使用以下代码删除已经创建的分区索引)
drop index 表名.索引名
2、在原来的索引字段上重建一个索引。
create clustered index CLU_StuNo -- 索引名称
on IE_Customs_Record(ID) -- 指定添加索引的表(添加索引的列)
on [PRIMARY]
3.删除分区函数
3.1.备份数据库!备份数据库!备份数据库! (对数据库动任何刀子前先备份)
3.2.删除分区函数所涉及的表,因为在建表的时候我们做了关联,所以删除主要是切断这个关联以及相关的文件。
3.3删除关联、删除分区文件 sql语句如下
select * from sys.partition_schemes --查询分区
select * from sys.partition_range_values --查询分区范围
select * from sys.partition_functions --查询分区函数
DROP PARTITION SCHEME 查询分区.[name]
DROP PARTITION FUNCTION 查询分区函数.[name]
--执行Drop语句后再做上面的查询应该已经查不到任何内容。
--删除分区文件
ALTER DATABASE [DataBase] REMOVE FILE [分区文件名];
ALTER DATABASE [DataBase] REMOVE FILEGROUP [分区文件组名]
4.删除2中创建的索引
附1: ssms时间统计
DBCC DROPCLEANBUFFERS --删除缓存区
DBCC FREEPROCCACHE --删除计划高速缓存中的元素
--方法一
set statistics profile on
set statistics io on
set statistics time on
--sql
set statistics profile off
set statistics io off
set statistics time off
--方法二
DECLARE @time DATETIME
SET @time= GETDATE()
--sql
SELECT DateDiff(ms,@time,GetDate())
附2: 分区命令
1.查看分区依据列的指定值所在的分区
--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000) --返回值是2,表示此值存在第2个分区
2.查看分区表中,每个非空分区存在的行数
select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount
from bigorder
group by $partition.bgPartitionFun(orderid)
3.查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)=2