sqlserver 表分区

什么是表分区

一般情况下，我们建立数据库表时，表数据都存放在一个文件里。

但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。

所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对数据库产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。


--XXXX 为数据库 

--1、创建数据库文件组

alter database XXXX add filegroup XXXX2020
alter database XXXX add filegroup XXXX2021
alter database XXXX add filegroup XXXX2022
alter database XXXX add filegroup XXXX2023
alter database XXXX add filegroup XXXX2024
alter database XXXX add filegroup XXXX2025

go
--2、添加存储文件

alter database XXXX add file 
(name=N'XXXX2020',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2020.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2020
alter database XXXX add file 
(name=N'XXXX2021',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2021.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2021
alter database XXXX add file 
(name=N'XXXX2022',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2022.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2022
alter database XXXX add file 
(name=N'XXXX2023',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2023.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2023
alter database XXXX add file 
(name=N'XXXX2024',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2024.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2024

alter database XXXX add file 
(name=N'XXXX2025',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2025.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2025
--分区存储
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [CreateTime](datetime) AS RANGE RIGHT FOR VALUES (N'2020-01-01T00:00:00', N'2021-01-01T00:00:00', N'2022-01-01T00:00:00', N'2023-01-01T00:00:00', N'2024-01-01T00:00:00', N'2025-01-01T00:00:00')


CREATE PARTITION SCHEME [CreateTime] AS PARTITION [CreateTime] TO ([PRIMARY], [XXXX2020], [XXXX2021], [XXXX2022], [XXXX2023], [XXXX2024], [XXXX2025])


CREATE CLUSTERED INDEX [ClusteredIndex_on_CreateTime_637401672488268535] ON [dbo].[XXX_TABLE]
(
    [CREATE_DATE]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [CreateTime]([CREATE_DATE])


DROP INDEX [ClusteredIndex_on_CreateTime_637401672488268535] ON [dbo].[XXX_TABLE]

COMMIT TRANSACTION

--后期 添加新的分区
go
--准备一个文件组来保存新分区的数据
alter database XXXX add filegroup XXXX2026
alter database XXXX add file 
(name=N'XXXX2026',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\XXXX2026.ndf',size=5Mb,filegrowth=5mb)
to filegroup XXXX2026
go

ALTER PARTITION SCHEME CreateTime  
NEXT USED XXXX2026 
--定义边界
ALTER PARTITION FUNCTION CreateTime() 
SPLIT RANGE ('2026-01-01T00:00:00') 



--分区合并 及删除分区
alter partition function CreateTime()  merge  range('2026-01-01T00:00:00')


--分区数据查询
select $partition.CreateTime(CREATE_DATE)  as Patition,COUNT(*) countRow  from XXX_TABLE
group  by $partition.CreateTime(CREATE_DATE)

--查询不同分区
SELECT * FROM XXX_TABLE WHERE $partition.CreateTime(CREATE_DATE) =  2

使用SQL Server Management Studio 管理工具创建分区：

选中表鼠标右键