SQL Server ->> 產生時間類型的Partition Function和Partition Scheme代碼

標籤：

有時工作中要建個分區函數，可是像日期這種分區函數要是搞個幾百個的值那不是要搞死我。於是寫了點代碼自動產生一個從1990年開始的按月的分區函數和對應的分區主題

 

USE [TestDB]GODECLARE @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 scriptSELECT @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 scriptSELECT @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‘

 

SQL Server ->> 產生時間類型的Partition Function和Partition Scheme代碼