SQL Server ->> 生成时间类型的Partition Function和Partition Scheme代码

有时工作中要建个分区函数，可是像日期这种分区函数要是搞个几百个的值那不是要搞死我。于是写了点代码自动生成一个从1990年开始的按月的分区函数和对应的分区主题

 

USE [TestDB]
GO

DECLARE @STR NVARCHAR(MAX)
SET @STR = 'CREATE PARTITION FUNCTION [PF_RangeByMonth_FromYear1990](DATETIME) AS 
            RANGE LEFT FOR VALUES ('

DECLARE @STR2 NVARCHAR(MAX)
SET @STR2 = 'CREATE PARTITION SCHEME [PS_RangeByMonth_FromYear1990] AS PARTITION [PF_RangeByMonth_FromYear1990] TO ('

--generate corresponding partition function script
SELECT @STR + STUFF((SELECT ''',''' + CONVERT(VARCHAR, DATEADD(MONTH,ID-1,'1990-01-01'), 121)
        FROM [dbo].[Numbers]
        WHERE ID <= DATEDIFF(MONTH,'1990-01-01','2040-01-01')+1
        ORDER BY [ID]
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)') 
,1,2,'') + ''')' + CHAR(13) as 'partition function script'

--generate corresponding partition scheme script
SELECT @STR2 + STUFF((SELECT +', [PRIMARY]' 
        FROM [dbo].[Numbers]
        WHERE ID <= DATEDIFF(MONTH,'1990-01-01','2040-01-01')+2
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)') 
,1,2,'') + ')' + CHAR(13) as 'partition scheme script'

 

--------------------- 2015/10/10 Update ---------------------------------------------------------------

万一需要自动生成一个按周或者特定天数一段段的这种，可以用下面的脚本

USE Mydb
GO

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t([NO] INT IDENTITY(1,1), cmd_type NVARCHAR(10),partial_cmd NVARCHAR(MAX))
GO

DECLARE @STR NVARCHAR(MAX)
SET @STR = 'CREATE PARTITION FUNCTION [PF_RangeByWeek_FromYear1990](DATETIME) AS 
            RANGE LEFT FOR VALUES ('

DECLARE @STR2 NVARCHAR(MAX)
SET @STR2 = 'CREATE PARTITION SCHEME [PS_RangeByWeek_FromYear1990] AS PARTITION [PF_RangeByWeek_FromYear1990] TO ('

INSERT #t(cmd_type, partial_cmd) VALUES('PF', @STR), ('PS', @STR2)

DECLARE @b_date DATETIME,
        @e_date DATETIME,
        @curr_date DATETIME,
        @end INT = 0,
        @time INT = 7,
        @i INT = 365

SELECT    @b_date = '1990-01-01',
        @e_date = '2040-01-01'
        

SELECT    @curr_date = @b_date,
        @i = CASE WHEN @i > DATEDIFF(DAY,@b_date,@e_date) THEN DATEDIFF(DAY,@b_date,@e_date) ELSE @i END


WHILE 1 = 1
BEGIN
PRINT @curr_date
INSERT #t(cmd_type, partial_cmd)
--generate corresponding partition function script
SELECT cmd_type, IIF(@curr_date = @b_date, RIGHT([partition function script],LEN([partition function script])-1), [partition function script])
FROM (
SELECT    'PF' cmd_type,
        STUFF((SELECT ''',''' + CONVERT(NVARCHAR(MAX), DATEADD(DAY,@time*(ID-1),@curr_date), 121)
        FROM [dbo].[Numbers]
        WHERE ID <= @i
        ORDER BY [ID]
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
,1,1,'') + '''' as 'partition function script') t

INSERT #t(cmd_type, partial_cmd)
--generate corresponding partition scheme script
SELECT cmd_type, IIF(@curr_date = @b_date, RIGHT([partition scheme script],LEN([partition scheme script])-1), [partition scheme script])
FROM (
SELECT  'PS' cmd_type,
        (SELECT ',' + ' [DATA]' 
        FROM [dbo].[Numbers]
        WHERE ID <= @i
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') as 'partition scheme script') t

IF @end = 1
BEGIN
    INSERT #t(cmd_type, partial_cmd) VALUES('PS',', [DATA]'),('PF',')'),('PS',')')
    BREAK
END

IF DATEADD(DAY,(@i*@time*2)-1,@curr_date)>=@e_date 
BEGIN
    SET @curr_date = DATEADD(DAY,@i*@time,@curr_date)
    SET @i = (DATEDIFF(DAY,@curr_date,@e_date)/@time)+2
    SET @end = 1
END
ELSE
BEGIN
    SET @curr_date = DATEADD(DAY,@i*@time,@curr_date)
END

END

SELECT partial_cmd
FROM #t
WHERE cmd_type = 'PF'
ORDER BY cmd_type, NO


SELECT partial_cmd
FROM #t
WHERE cmd_type = 'PS'
ORDER BY cmd_type, NO