sql server 自動分表 sql表分区

转载

索姆拉 2023-11-27 19:09:39

文章标签 sql server 自動分表分区表数据 sql 文章分类 SQL Server 数据库

常用SQL

查看当前系统中的分区情况

-- 查询所有分区占用情况
select $PARTITION.PartitionFunctionDateTime(Col) as 分区编号,
count(Id) as 记录数 from A group by $PARTITION.PartitionFunctionDateTime(Col)

其中 A 为表名，PartitionFunctionDateTime 为分区函数， Id为表中主键名.

复制表数据

insert into A select  * from B

分区函数

分区函数是数据库中的一个独立对象，它将表的行映射到一组分区，所以分区函数解决的是HOW的问题，即表如何分区的问题。创建分区函数时，必须指明数据分区的边界点以及分区依据列，这样便知道如何对表或索引进行分区。

每个boundary_value属于属于每个边界值间隔的哪一侧（左侧还是右侧）

-- PartitionFunctionDateTime 分区函数名称
CREATE PARTITION FUNCTION PartitionFunctionDateTime( datetime )
AS RANGE RIGHT
FOR VALUES ('2013-01-01 00:00:00','2014-01-01 00:00:00')

此例中是采用的时间分区方案

分区方案

CREATE PARTITION SCHEME [SchemeDateTime] AS PARTITION [PartitionFunctionDateTime] TO
([SECTION2013], [SECTION2014], [SECTION2015])

自动按年份递增的方式创建文件及文件组

-- 按年来创建分区

DECLARE
    @fileGroupName VARCHAR(200),
    @fileNamePath VARCHAR(200),
    @fileName VARCHAR(200),
    @databaseName VARCHAR(200),  -- 数据库名
    @nextYear VARCHAR(200),  -- 时间字段名字
    @nextRange VARCHAR(200),  -- 时间范围值
    @sql NVARCHAR(1000)


-- 数据库文件名字等
-- SET @nextYear=LEFT(REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':',''), 8) 
-- SET @nextYear=REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','')
-- SET @nextYear=CONVERT(varchar, GETDATE(), 120 )

-- 获取分区表
SELECT @nextYear =DATENAME(yyyy, CONVERT(datetime,MAX(value)))
FROM SYS.PARTITION_RANGE_VALUES PRV

SET @nextYear=CONVERT(int, @nextYear) + 1

-- PRINT @nextYear

-- 设置数据库名	
SET @databaseName='A'


-- 创建文件组，例如： SECTION2013
SET @fileGroupName='SECTION'+@nextYear
-- PRINT @fileGroupName

SET @sql='ALTER DATABASE ['+@databaseName+'] ADD FILEGROUP '+@fileGroupName
PRINT @sql
EXEC(@sql)


SET @fileName=N'DB' + @nextYear
SET @fileNamePath='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\' + @fileName +'.NDF'

SET @sql='ALTER DATABASE ['+@databaseName+'] ADD FILE (
NAME='''+@fileName+''',
FILENAME=N'''+@fileNamePath+'''
size=10MB, 
filegrowth=10%
) TO FILEGROUP'+'    '+@fileGroupName
PRINT @sql
PRINT 1
EXEC(@sql)
PRINT 2


--修改分区方案，用一个新的文件组用于存放下一新增的数据
SET @sql='ALTER PARTITION SCHEME [SchemeDateTime] NEXT USED'+'    '+@fileGroupName
EXEC(@sql)


--分区架构
PRINT 3

SET @nextRange=CONVERT(datetime,'1/1/'+@nextYear,101)

ALTER PARTITION FUNCTION [PartitionFunctionDateTime]()  --分区函数
SPLIT RANGE (@nextRange)

其他

-- 获取分区函数中分隔参数
SELECT  * FROM SYS.PARTITION_RANGE_VALUES ORDER BY VALUE DESC

-- 查询出分区界限中的最大值
SELECT MAX(value) FROM SYS.PARTITION_RANGE_VALUES PRV

--  Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)