sql server分区

一.分区脚本

--生成分区脚本
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

 

参考链接: https://www.cnblogs.com/goodluckily/p/14420744.html