sql server 表分区

表分区主要分为三步：

1、创建分区函数

2、创建分区架构

3、创建表并关联分区架构

准备工作：（为数据库创建文件组和文件）

创建文件组语法：

ALTER DATABASE 数据库名 ADD FILEGROUP 文件组名;

如：ALTER DATABASE SMMP ADD FILEGROUP FG0;

创建文件语法：

ALTER DATABASE SMMP ADD FILE(NAME = '逻辑文件名',FILENAME = '物理文件名') TO FILEGROUP 文件组名;

如：ALTER DATABASE SMMP ADD FILE(NAME = 'FILE_0',FILENAME = 'D:\sqlserverdata\SMMP\SMMP_0.ndf') TO FILEGROUP FG0;

创建分区函数

语法：

CREATE PARTITION FUNCTION [函数名](参数类型)
AS RANGE [RIGHT | LEFT] FOR VALUES
(
      分割值1,分隔值2,分隔值3,....n

)

注意：分隔值必须是有序的，并且n的个数不能大于999。left 表示从左边开始≤ 分割值；right 表示从右边开始≥分割值。

如：

CREATE PARTITION FUNCTION [pf_ModID](int)
AS RANGE RIGHT FOR VALUES
(
 1, 2, 3, 4, 5

)

分成6个区域

创建分区架构

语法：

CREATE PARTITION SCHEME [分区架构]
AS PARTITION [分区函数] TO
(
    [文件组1], [文件组2], [文件组3],...n

)

注意：文件组的个数必须等于分区函数间隔值个数加1，并且对应间隔值分割成的区域

如：

CREATE PARTITION SCHEME [ps_ModID]
AS PARTITION [pf_ModID] TO
(
 [FG0], [FG1], [FG2], [FG3], [FG4], [FG5]

)

对应6个文件组

创建表并关联分区架构

语法：

 create table 表
(
 [id] [int] IDENTITY(1,1) NOT NULL,
 [字段0] [int] NOT NULL,

 [字段1] [int] NOT NULL

)  on 分区架构(字段)

注意：最后的参数字段必须和分区函数的参数个数和类型一致

如：

create table Tweets
(
 [TweetID] [int] IDENTITY(1,1) NOT NULL,
 [ModID] [int] NOT NULL

)  on ps_ModID([ModID])

 

其它：

-- 查询文件组
SELECT * FROM sys.filegroups
-- 查询文件
SELECT * FROM sys.database_files
-- 获取所有文件信息
select * from sysfiles

-- 查询文件组和文件
SELECT df.[name],
       df.physical_name,
       df.[size],
       df.growth,
       f.[name][filegroup],
       f.is_default
FROM sys.database_files df JOIN sys.filegroups f
ON df.data_space_id = f.data_space_id
go

--清空数据文件

DBCC SHRINKFILE(文件名,EMPTYFILE)
go
--删除数据文件

ALTER DATABASE 数据库
REMOVE FILE 文件名
go
--删除文件组

ALTER DATABASE 数据库
REMOVE FILEGROUP 文件组名

go

-- 查询分区数据

SELECT $PARTITION.[分区方案](字段) AS PATITION , * FROM 分区表
如：SELECT $PARTITION.[pf_ModID](UserModID) AS PATITION , * FROM Tweets

 

-- 批量添加文件组和文件

create procedure sp_CreateFilegroupsAndFiles
(
 @count int=0, -- 文件组的数量
 @path varchar(50)=null -- 文件存储路径 格式例如：‘D:\sqlserverdata\’
)
as
begin
  declare @databaseName varchar(50) -- 数据库名称
  declare @group_name varchar(10)
  declare @file_name varchar(10)
  declare @file_filename varchar(50)
  declare @i int
  set @databaseName = db_name() --获取当前数据库名称
  set @i = 0
  -- 获取数据库路径
  if @path is null
   begin
    select @path=replace(physical_name,'.mdf','') from sys.database_files where physical_name like '%.mdf'
   end
  else
   begin
    set @path=@path+@databaseName
   end
  --print @path
  while @i<@count
   begin
     set @group_name='FG'+cast(@i as varchar(20))
     set @file_name='File'+cast(@i as varchar(20))
     set @file_filename=@path+'_'+cast(@i as varchar(5))+'.ndf'
     --print @file_filename
     -- 创建文件组
     exec('ALTER DATABASE '+@databaseName+' ADD FILEGROUP '+@group_name+';')
     -- 为文件组添加文件
     exec('ALTER DATABASE '+@databaseName+' ADD FILE(NAME = '''+@file_name+''',FILENAME = '''+@file_filename+''') TO FILEGROUP '+@group_name+';');
     set @i=@i+1
   end
   --SELECT * FROM sys.filegroups
   --SELECT * FROM sys.database_files
end
go

exec sp_CreateFilegroupsAndFiles
@count = 10, -- 文件组的数量
@path = '' -- 数据库文件路径，可不传，默认和.mdf文件在同一目录下