SqlServer2019数据库分表
场景：MSSql数据库中某个表数据量大，需要进行分表操作
主备说明：摘抄：https://blog.csdn.net/songjuntao8/article/details/123215567
分库
垂直切分(纵向切分)
比如说一个电商平台，里面包含了订单系统，物流系统，仓储系统等，原先是用的同一个数据库，现在我们可以设计成每个系统都有自己的数据库服务器，不公用同一个数据库，不同系统之间的数据可以通过API调用来，这样压力就会小很多。这可以理解为垂直切分。
水平切分(横向切分)
比如订单系统中数据量很大，导致了数据库的性能问题，我们可以对订单系统数据库进行水平切分。水平分库之后数据库的结构是完全一致的，只是存储的数据不一样而已，比如说一个存储去年的，一个存储今年，，，，可以按照时间来进行切分，如果业务要求查询的数据中一部分在去年，一部分在今年，那就需要程序员在程序中进行处理了。当然实际情况根据实际业务，这里只是做一个假设。还可以按照区域进行切分，比如外卖平台，A数据库存储A区域的数据，B数据库存储B区域的数据。
还可以简单总结一下就是垂直切分就是把不同的表组合成不同的库，水平分库就是将相同的表分成不同的库
分表
垂直分表(纵向分表)
某些字段的数据不常用，但是又包含在这张表中，而且这些字段很损耗性能(因为存在IO压力)；就可以进行垂直切分，一个表存储这个很损耗性能或者不常用的字段，其他比较常用的字段存储在另一个表，两个表可以直接通过两个主键关联（建议使用Guid作为主键）；也可以通过主外键关系来关联！
水平分表
比如说一个订单表包含的数据量非常大；有一些数据（两年前的订单数据）也存储在这张表； 就可以水平切分，多个表的表结构是完全一致，但是数据不一样，每个表记录不同时间段的数据(这是以时间为维度进行切分的)；比如房产系统（外卖系统），这都可以以区域为维度来进行切分，比如不同的表存储不同区域的数据；还会可以通过指定的算法来进行表的水品切分--把数据均匀分配，慎用。
1.基础数据（新建一个Test数据库和User数据表，往里头循环插入数据,这个我是要准备按照CloseDate去分割数据的）
DECLARE @num INT = 1 WHILE(@num < 1000) BEGIN INSERT dbo.[User] ( Name, DellreceiveDate, CloseDate ) VALUES ( '张三' + CONVERT(VARCHAR(50),@num), -- Name - nchar(10) DATEADD(DAY,@num,'2019-01-01'), -- DellreceiveDate - date DATEADD(DAY,@num,'2020-06-01') -- CloseDate - date ) SET @num = @num + 1 END
方式一：1.SQL创建（这个弄到后面，我发现由于表已经创建了，执行报错，然后还可以进行使徒的创建-这个比较稳，还有计划任务）
2.SQL语句（注：这里后面）
--创建文件组 ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2020 ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2021 ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2022 ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2023 --给文件组添加文件 ALTER DATABASE TEST ADD FILE (NAME=N'TEST_User_FileGroup2020',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2020.mdf',SIZE=5MB, FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2020 ALTER DATABASE TEST ADD FILE(NAME=N'TEST_User_FileGroup2021',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2021.mdf',SIZE=5MB, FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2021 ALTER DATABASE TEST ADD FILE(NAME=N'TEST_User_FileGroup2022',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2022.mdf',SIZE=5MB, FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2022 ALTER DATABASE TEST ADD FILE(NAME=N'TEST_User_FileGroup2023',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2023.mdf',SIZE=5MB, FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2023 --查看数据库中文件情况 SELECT name,type_desc,physical_name,state_desc,size,growth FROM sys.database_files --创建分区函数 CREATE PARTITION FUNCTION partition_function_user(DATETIME) AS RANGE RIGHT FOR VALUES( '2020-01-01', '2021-01-01', '2022-01-01' ) --查询分区函数 SELECT * FROM sys.partition_functions --删除分区函数partition_function_user DROP PARTITION FUNCTION partition_function_user ---创建分区方案 CREATE PARTITION SCHEME TestUserScheme AS PARTITION partition_function_user TO( [PRIMARY], TEST_User_FileGroup2020, TEST_User_FileGroup2021, TEST_User_FileGroup2022 ) --查询分区方案 SELECT * FROM sys.partition_schemes --删除分区方案 Scheme_DateTime DROP PARTITION SCHEME Scheme_DateTime --创建分区表 CREATE TABLE [dbo].[User]( Id INT, Name varchar(10), DellreceiveDate DATETIME, CloseDate DATETIME ) ON Scheme_DateTime(CloseDate)
--这里把分区函数的4个段对应到4个文件组上。 --然后把表链接到分区方案，这里使用CloseDate主键作为分区列。 --ALTER TABLE dbo.[User] add CONSTRAINT [PK_CloseDate] PRIMARY KEY CLUSTERED (CloseDate) --ON TestSPScheme(SPNo)
2.视图创建
1）右键数据库=》属性=》文件组=》添加文件组（这步可以直接参考1中的创建文件组命令，我是按照名字结尾为年份的）
注：查看文件对应的【文件组】（这个一创建就无法修改文件组，会导致分表执行报错）
2）右键需要分区的表=》存储=》创建分区（这个就按向导下一步填写相关数据了，开始比较卡的样子，阿西吧）
选择根据分区的字段
选择分区函数
选择分区方案
设置边界
选择前面1）创建的文件组（注：这里地下好像需要多选个【PRIMARY】？为下一个做准备的？）
创建完成，也可以勾选立即运行（我这里另存为脚本桌面瞅瞅）
3.查看结果【$PARTITION.分区函数(分区字段)】
SELECT $PARTITION.partition_function_user001(CloseDate) AS 分区编号,COUNT(1) AS 记录数 FROM dbo.[User] GROUP BY $PARTITION.partition_function_user001(CloseDate)
--查询已存在的分区方案 select * from sys.partition_schemes --查询已存在的分区函数 select * from sys.partition_functions --根据临界值和分区函数名称查询某个时间的分区函数是否已存在 select b.value,* from sys.partition_functions a,sys.partition_range_values b WHERE a.function_id=b.function_id AND name='f_RECHARGE_INFO' AND CONVERT(varchar(100), b.value, 23)='2019-10-01' --查询某个时间的分区编号 select $PARTITION.partition_function_user001('2022-12-01') --根据分区编号查询表数据,CREATEDATE表示分区表的时间属性字段 select * from dbo.[User] WHERE $PARTITION.partition_function_user001(CloseDate)=3 --查询分区编号和对应的记录数 SELECT $PARTITION.partition_function_user001(CloseDate) AS 分区编号,COUNT(1) AS 记录数 FROM dbo.[User] GROUP BY $PARTITION.partition_function_user001(CloseDate)
补充动态生成：
--新建存储过程，每年新增一个文件组
CREATE PROC CtiUser_AutoExted_Partition_Year
AS
BEGIN
DECLARE @filePath VARCHAR(100),--文件路径
@fileName VARCHAR(100), --文件名称
@fileSize VARCHAR(100), --文件大小
@fileGrowth VARCHAR(100), --文件增长
@fileMaxLimit VARCHAR(100), --文件最大限制
@fileGroupName VARCHAR(100), --文件组名称
@database VARCHAR(100), --操作数据库
@currentDateTimeByDay VARCHAR(100), --当前时间，日
@schemeName VARCHAR(100), --分区方案名称
@partitionName VARCHAR(100), --分区函数名称
@sql VARCHAR(400) -- 赋值文件属性
SET @fileSize = '5MB';
SET @fileGrowth = '1MB';
SET @fileMaxLimit = 'unlimited';
SET @currentDateTimeByDay = CONVERT(varchar(10),datepart(YYYY,GETDATE()));
SET @fileName = 'TEST_User_FileGroup' + @currentDateTimeByDay;
SET @filePath = 'D:\SQL\UserSQL\';
--赋值数据库属性
SET @database = 'TEST';
--赋值文件组属性
SET @fileGroupName = 'TEST_User_FileGroup' + @currentDateTimeByDay
--赋值分区属性
SET @schemeName='TestUserScheme001'
SET @partitionName = 'partition_function_user001()'
--创建文件组
SET @sql = 'ALTER DATABASE ' + @database + ' add filegroup ' + @fileGroupName;
EXEC(@sql);
--创建文件，并绑定文件组
SET @sql = 'ALTER DATABASE ' + @database + ' add file (name=''' + @fileName + ''','
+ 'filename=''' + @filePath + '\'
+ @fileName + '.mdf'',' + 'size = ' + @fileSize + ',' + 'filegrowth =' + @fileGrowth + ','
+ 'maxsize =' + @fileMaxLimit + '' + ')' + 'to filegroup ' + @fileGroupName;
EXEC(@sql);
--修改分区方案
SET @sql = 'alter partition scheme ' + @schemeName + ' next used ' + @fileGroupName + '';
EXEC(@sql);
--修改分区函数
SET @sql = 'alter partition function ' + @partitionName
+ ' split range (N''' + CONVERT(varchar(10),datepart(YYYY,GETDATE())) + ''')';
EXEC(@sql);
END
GO
摘自：https://blog.csdn.net/shanghaimoon/article/details/123344746
感谢：https://blog.csdn.net/songling418/article/details/13596707
https://www.cnblogs.com/kissdodog/p/3156166.html
https://blog.csdn.net/lanxingbudui/article/details/84792123
https://www.csdn.net/tags/MtzaAg5sMzcyNzYtYmxvZwO0O0OO0O0O.html
