SqlServer数据库分区分表实例分享（有详细代码和解释）
数据库单表数据量太⼤可能会导致数据库的查询速度⼤⼤下降（感觉都是千万级以上的数据表了），可以采取分区分表将⼤表分为⼩表解决（当然这只是其中⼀种⽅法），⽐如数据按⽉、按年分表，最后可以使⽤视图将⼩表重新并为总的虚拟表，其实并不影响上层程序的使⽤（程序也许都不知道分表了）。
主要步骤：
1、新建⽂件组，将数据表⽂件保存路径指向相应⽂件组（应将⽂件组和⽂件放⼊不同的磁盘中，甚⾄不同服务器形成分布式数据库，因为数据的读取瓶颈很⼤程度在于磁盘的的读写速度，多个磁盘存放⼀个表可以负载均衡）
2、设置分区函数（声明分区的标准）
3、设置分区⽅案（即哪些区域使⽤哪个分区函数，形成完整的分区⽅案）
4、给新表或现有表设置分区⽅案
5、建⽴视图
详细步骤（看需求可选）：
⼀、数据库状态备份和恢复
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = 'AdventureWorks.bak'
WITH FORMAT
---- 恢复
RESTORE DATABASE AdventureWorks
FROM DISK = 'AdventureWorks.bak'
WITH REPLACE
GO
⼆、⽂件组和⽂件操作
添加⽂件组
USE [master]
GO
ALTER DATABASE ZHH ADD FILEGROUP [⽂件组名称]
Go
添加⽂件并把其指向指定⽂件组
USE master;
GO
ALTER DATABASE 数据库名
ADD FILE(
NAME=N'⽂件名',
FILENAME='存放路径', //如：E:\201109.NDF（精确到⽂件名）⽂件组存放与不同磁盘可以提⾼IO读写效率（多个磁头并发）
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)TO FILEGROUP [⽂件组名]
Go
修改⽂件（可选）
USE master;
GO
ALTER DATABASE 数据库名
MODIFY FILE
(NAME = ⽂件名,
SIZE = 20MB);  //可以修改所有属性，列举即可
GO
删除⽂件（可选）
ALTER DATABASE 数据库名 REMOVE FILE [⽂件组名]
三、分区函数和分区⽅案
分区函数
⽤于规范如何分区的标准，如已哪列进⾏为标准分区、分区的⽅式（按时间、ID等）、分区的具体界限（⼀般来说，界限指标数要⽐分区数少1，⼀⼑则有两段）
USE 数据库名
GO
CREATE PARTITION FUNCTION 分区函数名 (指标列的数据类型)  //如：datetime、int
AS RANGE RIGHT  //右边界切分，默认为LEFT
FOR VALUES (划分界限)  //如时间划分('2003/01/01', '2004/01/01')，两个时间界限可划分出三个分区
GO
分区⽅案
⽤于将已经建⽴好的分区函数组织成完整的⽅案，为每个分区分配存储位置
Use 数据库名
go
create  partition  scheme 分区⽅案名
as partition  分区函数
to(⽂件组1,⽂件组2,⽂件组3,...) //注意分区数要与实际分区⼀致
go
在原有的基础上添加分区（可选）
use 数据库名
go
alter partition scheme ps_OrderDate  next used [FG4]    //修改分区⽅案ps_OrderDate，定义新新分区使⽤FG4⽂件组
alter partition function pf_OrderDate() split range('2005/01/01')  //修改分区函数pf_OrderDate，在末尾添加界限'2005/01/01'
go
为现有表设置分区⽅案（可选）
//为AutoBench表的InsertTime列创建新聚集索引，并绑定Scheme_DateTime分区⽅案
CREATE CLUSTERED INDEX IX_CreateDate ON AutoBench (InsertTime)
ON Scheme_DateTime (InsertTime)
注：如原来主键有聚众索引要将其改为⾮聚集索引，才可添加新聚众索引
//删除原主键上的聚集索引PK_Product
ALTER TABLE Product DROP CONSTRAINT PK_Product
//重新创建主键⾮聚集索引PK_Product
ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC)
上⾯语句也可直接在索引属性中将聚集改为⾮聚集
为新建表设置分区⽅案（可选）
//创建表格Order，并设置Scheme_DateTime分区⽅案，指标列为OrderDate
CREATE TABLE [Order]
(
OrderID INT IDENTITY(1,1) NOT NULL,
UserID INT NOT NULL,
TotalAmount DECIMAL(18,2) NULL,
OrderDate DATETIME NOT NULL
) ON Scheme_DateTime (OrderDate)
查询分区数据
四、其他操作
查询分区数据
$partition函数--为任何指定的分区函数返回分区号，⼀组分区列值将映射到该分区号中
语法： [ database_name. ] $PARTITION.partition_function_name(expression)
参数： database_name 包含分区函数的数据库的名称。
partition_function_name 对其应⽤⼀组分区列值的任何现有分区函数的名称。
expression 其数据类型必须匹配或可隐式转换为其对应分区列数据类型的表达式。 expression 也可以是当前参与partition_function_name 的分区列的名称。返回类型： int (分区号)
//筛选使⽤Function_DateTime作为分区函数的AutoBench表，以InsertTime作为指标列的第⼆个分区的所有数据
select * from AutoBench WHERE $PARTITION.Function_DateTime(InsertTime) = 2
合并分区
//删除Sales数据库下的分区函数pf_OrderDate中的'2003/01/01'界限，以次界限划分的两个分区合并，分区号⼀次减1
use Sales
go
alter partition function pf_OrderDate() merge range('2003/01/01')
go
查看系统视图
select * from sys.partition_functions  //分区函数
select * from sys.partition_range_values    //分区⽅案
select * from sys.partition_schemes    //边界值点
五、⾃动分区
可以采⽤SQL Server代理中的作业定期⾃动执⾏分区脚本，实现⾃动分区（如每⽉结束⾃动执⾏按⽉分区的操作）
⾃动分区测试脚本
DECLARE
@fileGroupName VARCHAR(20),    --⽂件组名（格式为:FG+@Month）
@fileName VARCHAR(20),    --⽂件名（格式为:F+@Month）
@filePath VARCHAR(100),        --⽂件存放路径（格式为：存放⽬录路径+@fileName.ndf）
@dataBaseName VARCHAR(20),    --数据库名
@Month VARCHAR(10),        --当前时间年⽉（格式为:yyyymm）
@schemeName VARCHAR(20),    --分区⽅案名
@partFunctionName VARCHAR(20),    --分区函数名
@limit VARCHAR(10)    --分区界限（以时间分区则为时间字符串，格式为:mm/dd/yyyy)
SET @fileGroupName='FG201805'
SET @Month=CONVERT(varchar(10),GETDATE(),112)
SET @fileName=N'F201805'
SET @filePath='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\F201805.ndf'
SET @dataBaseName='Chassis'
SET @schemeName='Scheme_DateTime'
SET @partFunctionName='Function_DateTime'
SET @limit=CONVERT(varchar(10),GETDATE(),101)
--语句要指明需要操作的数据库
if exists(select * from Chassis.sys.filegroups where name=@fileGroupName)
begin
print '⽂件组存在，不需添加'
end
else
begin
exec('ALTER DATABASE '+@dataBaseName+' ADD FILEGROUP ['+@fileGroupName+']')
sqlserver备份表语句
print '新增⽂件组'+@fileGroupName
end
if exists(select * from Chassis.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath))
begin
print 'ndf⽂件存在，不需添加'
end
else
begin
exec('ALTER DATABASE '+@dataBaseName+' ADD FILE(NAME ='''+@fileName+''',FILENAME = '''+@filePath+''')TO FILEGROUP ['+@fileGroupName+']')
print '添加⽂件'+@fileName+'⾄⽂件组'+@fileGroupName
end
if exists(select * from sys.partition_schemes where name=@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区⽅案，指定下⼀分区的⽂件组'
end
else
begin
print '分区⽅案不存在'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_functions where name=@partFunctionName))
begin
if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name='Scheme_DateTime') and value=CONVERT(datetime,''+@limit+'',101))            begin
print '界限已存在'
end
else
begin
exec('alter partition function '+@partFunctionName+'() split range('''+@limit+''')')
print '修改分区函数，添加划分界限为：'+@limit
end
end
else
begin
print '分区函数不存在'
end
这只是本⼈的测试脚本，仅供参考~ 如有错漏请⼤佬指导

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。