clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798802 fiddles created (41845 in the last week).

DECLARE @today date = GETDATE(), @start date = DATEFROMPARTS(YEAR(GETDATE()),1,1), @end_next_month date = EOMONTH(GETDATE(), 1), @q char(1) = char(39), @crlf char(2) = char(13) + char(10), @pf nvarchar(255) = N'CREATE PARTITION FUNCTION pf', @rr nvarchar(255) = N'(datetime) AS RANGE RIGHT FOR VALUES(', @partSchemes nvarchar(max) = N'', @partFunctions nvarchar(max) = N'', @vschemas nvarchar(max) = N'', @vtables nvarchar(max) = N'', @dates nvarchar(max) = N'', @inserts nvarchar(max) = N'', @sql nvarchar(max); /* table names + date ranges for the partition functions */ DECLARE @p table(p nvarchar(128), s datetime, e datetime); INSERT @p(p,s,e) VALUES (N'BareMinimum', @start, @end_next_month), (N'Reasonable', @start, DATEADD(YEAR, 1, @end_next_month)), (N'VeryPrepared', @start, DATEADD(YEAR, 10, @end_next_month)), (N'Ludicrous', @start, DATEADD(YEAR, 40, @end_next_month)); /* generate schema, partition scheme, and table commands */ SELECT @vschemas = STRING_AGG(CONCAT(N'EXEC sys.sp_executesql N''CREATE SCHEMA ',p,' AUTHORIZATION dbo;'';'), @crlf), @partSchemes = STRING_AGG(CONCAT(N'CREATE PARTITION SCHEME ps', p,N' AS PARTITION pf', p,N' ALL TO ([PRIMARY]);'), @crlf), @vtables = STRING_AGG(CONCAT(N'CREATE TABLE ',p,'.', p,'(LogDate datetime) ON ps',p,'(LogDate);'), @crlf) FROM @p; /* generate partition function commands with explicit dates */ ;WITH x AS ( SELECT p, s, e FROM @p UNION ALL SELECT p, DATEADD(DAY, 1, s), e FROM x WHERE s < e ) SELECT @partFunctions += CONCAT(@crlf, @pf, p, @rr, STRING_AGG(CONVERT(nvarchar(max), CONCAT(@q, CONVERT(char(8), s, 112), @q)), ',') WITHIN GROUP (ORDER BY s), ');') FROM x GROUP BY p ORDER BY p OPTION (MAXRECURSION 0); /* generate 4 insert commands with identical values */ ;WITH d(d) AS ( SELECT CONVERT(datetime, @today) UNION ALL SELECT DATEADD(DAY, -1, d) FROM d WHERE d > @start ), t(s,p) AS ( SELECT DATEADD(HOUR, h, d), p FROM d CROSS JOIN (VALUES(6),(12),(18)) AS h(h) CROSS JOIN @p AS p ) SELECT @inserts += CONCAT(N'INSERT ',p,N'.',p,N'(LogDate) VALUES(', STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(CONVERT(varchar(23), s, 126), @q)), '),(') WITHIN GROUP (ORDER BY s), ');') FROM t GROUP BY p OPTION (MAXRECURSION 366); PRINT @partFunctions; PRINT @partSchemes; PRINT @vschemas; PRINT @vtables; PRINT @inserts;
CREATE PARTITION FUNCTION pfBareMinimum(datetime) AS RANGE RIGHT FOR VALUES('20220101','20220102','20220103','20220104','20220105','20220106','20220107','20220108','20220109','20220110','20220111','20220112','20220113','20220114','20220115','20220116','20220117','20220118','20220119','20220120','20220121','20220122','20220123','20220124','20220125','20220126','20220127','20220128','20220129','20220130','20220131','20220201','20220202','20220203','20220204','20220205','20220206','20220207','20220208','20220209','20220210','20220211','20220212','20220213','20220214','20220215','20220216','20220217','20220218','20220219','20220220','20220221','20220222','20220223','20220224','20220225','20220226','20220227','20220228','20220301','20220302','20220303','20220304','20220305','20220306','20220307','20220308','20220309','20220310','20220311','20220312','20220313','20220314','20220315','20220316','20220317','20220318','20220319','20220320','20220321','20220322','20220323','20220324','20220325','20220326','20220327','20220328','20220329','20220330','20220331','20220401','20220402','20220403','20220404','20220405','20220406','20220407','20220408','20220409','20220410','20220411','20220412','20220413','20220414','20220415','20220416','20220417','20220418','20220419','20220420','20220421','20220422','20220423','20220424','20220425','20220426','20220427','20220428','20220429','20220430','20220501','20220502','20220503','20220504','20220505','20220506','20220507','20220508','20220509','20220510','20220511','20220512','20220513','20220514','20220515','20220516','20220517','20220518','20220519','20220520','20220521','20220522','20220523','20220524','20220525','20220526','20220527','20220528','20220529','20220530','20220531'); CREATE PARTITION FUNCTION pfLudicrous(datetime) AS RANGE RIGHT FOR VALUES('20220101','20220102','20220103','20220104','20220105','20220106','20220107','20220108','20220109','20220110','20220111','20220112','20220113','20220114','20220115','20220116','20220117','20220118','20220119','20220120','20220121','20220122','20220123','20220124','20220125','20220126','20220127','20220128','20220129','20220130','20220131','20220201','20220202','20220203','20220204','20220205','20220206','20220207','20220208','20220209','20220210','20220211','20220212','20220213','20220214','20220215','20220216','20220217','20220218','20220219','20220220','20220221','20220222','20220223','20220224','20220225','20220226','20220227','20220228','20220301','20220302','20220303','20220304','20220305','20220306','20220307','20220308','20220309','20220310','20220311','20220312','20220313','20220314','20220315','20220316','20220317','20220318','20220319','20220320','20220321','20220322','20220323','20220324','20220325','20220326','20220327','20220328','20220329','20220330','20220331','20220401','20220402','20220403','20220404','20220405','20220406','20220407','20220408','20220409','20220410','20220411','20220412','20220413','20220414','20220415','20220416','20220417','20220418','20220419','20220420','20220421','20220422','20220423','20220424','20220425','20220426','20220427','20220428','20220429','20220430','20220501','20220502','20220503','20220504','20220505','20220506','20220507','20220508','20220509','20220510','20220511','20220512','20220513','20220514','20220515','20220516','20220517','20220518','20220519','20220520','20220521','20220522','20220523','20220524','20220525','20220526','20220527','20220528','20220529','20220530','20220531','20220601','20220602','20220603','20220604','20220605','20220606','20220607','20220608','20220609','20220610','20220611','20220612','20220613','20220614','20220615','20220616','20220617','20220618','20220619','20220620','20220621','20220622','20220623','20220624','20220625','20220626','20220627','20220628','20220629','20220630','20220701','20220702','20220703','20220704','20220705','20220706','20220707','20220708','20220709','20220710','20220711','20220712','20220713','20220714','20220715','20220716','20220717','20220 CREATE PARTITION SCHEME psBareMinimum AS PARTITION pfBareMinimum ALL TO ([PRIMARY]); CREATE PARTITION SCHEME psReasonable AS PARTITION pfReasonable ALL TO ([PRIMARY]); CREATE PARTITION SCHEME psVeryPrepared AS PARTITION pfVeryPrepared ALL TO ([PRIMARY]); CREATE PARTITION SCHEME psLudicrous AS PARTITION pfLudicrous ALL TO ([PRIMARY]); EXEC sys.sp_executesql N'CREATE SCHEMA BareMinimum AUTHORIZATION dbo;'; EXEC sys.sp_executesql N'CREATE SCHEMA Reasonable AUTHORIZATION dbo;'; EXEC sys.sp_executesql N'CREATE SCHEMA VeryPrepared AUTHORIZATION dbo;'; EXEC sys.sp_executesql N'CREATE SCHEMA Ludicrous AUTHORIZATION dbo;'; CREATE TABLE BareMinimum.BareMinimum(LogDate datetime) ON psBareMinimum(LogDate); CREATE TABLE Reasonable.Reasonable(LogDate datetime) ON psReasonable(LogDate); CREATE TABLE VeryPrepared.VeryPrepared(LogDate datetime) ON psVeryPrepared(LogDate); CREATE TABLE Ludicrous.Ludicrous(LogDate datetime) ON psLudicrous(LogDate); INSERT BareMinimum.BareMinimum(LogDate) VALUES('2022-01-01T06:00:00'),('2022-01-01T12:00:00'),('2022-01-01T18:00:00'),('2022-01-02T06:00:00'),('2022-01-02T12:00:00'),('2022-01-02T18:00:00'),('2022-01-03T06:00:00'),('2022-01-03T12:00:00'),('2022-01-03T18:00:00'),('2022-01-04T06:00:00'),('2022-01-04T12:00:00'),('2022-01-04T18:00:00'),('2022-01-05T06:00:00'),('2022-01-05T12:00:00'),('2022-01-05T18:00:00'),('2022-01-06T06:00:00'),('2022-01-06T12:00:00'),('2022-01-06T18:00:00'),('2022-01-07T06:00:00'),('2022-01-07T12:00:00'),('2022-01-07T18:00:00'),('2022-01-08T06:00:00'),('2022-01-08T12:00:00'),('2022-01-08T18:00:00'),('2022-01-09T06:00:00'),('2022-01-09T12:00:00'),('2022-01-09T18:00:00'),('2022-01-10T06:00:00'),('2022-01-10T12:00:00'),('2022-01-10T18:00:00'),('2022-01-11T06:00:00'),('2022-01-11T12:00:00'),('2022-01-11T18:00:00'),('2022-01-12T06:00:00'),('2022-01-12T12:00:00'),('2022-01-12T18:00:00'),('2022-01-13T06:00:00'),('2022-01-13T12:00:00'),('2022-01-13T18:00:00'),('2022-01-14T06:00:00'),('2022-01-14T12:00:00'),('2022-01-14T18:00:00'),('2022-01-15T06:00:00'),('2022-01-15T12:00:00'),('2022-01-15T18:00:00'),('2022-01-16T06:00:00'),('2022-01-16T12:00:00'),('2022-01-16T18:00:00'),('2022-01-17T06:00:00'),('2022-01-17T12:00:00'),('2022-01-17T18:00:00'),('2022-01-18T06:00:00'),('2022-01-18T12:00:00'),('2022-01-18T18:00:00'),('2022-01-19T06:00:00'),('2022-01-19T12:00:00'),('2022-01-19T18:00:00'),('2022-01-20T06:00:00'),('2022-01-20T12:00:00'),('2022-01-20T18:00:00'),('2022-01-21T06:00:00'),('2022-01-21T12:00:00'),('2022-01-21T18:00:00'),('2022-01-22T06:00:00'),('2022-01-22T12:00:00'),('2022-01-22T18:00:00'),('2022-01-23T06:00:00'),('2022-01-23T12:00:00'),('2022-01-23T18:00:00'),('2022-01-24T06:00:00'),('2022-01-24T12:00:00'),('2022-01-24T18:00:00'),('2022-01-25T06:00:00'),('2022-01-25T12:00:00'),('2022-01-25T18:00:00'),('2022-01-26T06:00:00'),('2022-01-26T12:00:00'),('2022-01-26T18:00:00'),('2022-01-27T06:00:00'),('2022-01-27T12:00:00'),('2022-01-27T18:00:00'),('2022-01-28T06:00:00'),('2022-01-28T12:00:00'),('2022-01-28T18:00:00'),('2022-01-29T06:00:00'),('2022-01-29T12:00:00'),('2022-01-29T18:00:00'),('2022-01-30T06:00:00'),('2022-01-30T12:00:00'),('2022-01-30T18:00:00'),('2022-01-31T06:00:00'),('2022-01-31T12:00:00'),('2022-01-31T18:00:00'),('2022-02-01T06:00:00'),('2022-02-01T12:00:00'),('2022-02-01T18:00:00'),('2022-02-02T06:00:00'),('2022-02-02T12:00:00'),('2022-02-02T18:00:00'),('2022-02-03T06:00:00'),('2022-02-03T12:00:00'),('2022-02-03T18:00:00'),('2022-02-04T06:00:00'),('2022-02-04T12:00:00'),('2022-02-04T18:00:00'),('2022-02-05T06:00:00'),('2022-02-05T12:00:00'),('2022-02-05T18:00:00'),('2022-02-06T06:00:00'),('2022-02-06T12:00:00'),('2022-02-06T18:00:00'),('2022-02-07T06:00:00'),('2022-02-07T12:00:00'),('2022-02-07T18:00:00'),('2022-02-08T06:00:00'),('2022-02-08T12:00:00'),('2022-02-08T18:00:00'),('2022-02-09T06:00:00'),('2022-02-09T12:00:00'),('2022-02-09T18:00:00'),('2022-02-10T06:00:00'),('2022-02-10T12:00:00'),('2022-02-10T18:00:00'),('2022-02-11T06:00:00'),('2022-02-11T12:00:00'),('2022-02-11T18:00:00'),('2022-02-12T06:00:00'),('2022-02-12T12:00:00'),('2022-02-12T18:00:00'),('2022-02-13T06:00:00'),('2022-02-13T12:00:00'),('2022-02-13T18:00:00'),('2022-02-14T06:00:00'),('2022-02-14T12:00:00'),('2022-02-14T18:00:00'),('2022-02-15T06:00:00'),('2022-02-15T12:00:00'),('2022-02-15T18:00:00'),('2022-02-16T06:00:00'),('2022-02-16T12:00:00'),('2022-02-16T18:00:00'),('2022-02-17T06:00:00'),('2022-02-17T12:00:00'),('2022-02-17T18:00:00'),('2022-02-18T06:00:00'),('2022-02-18T12:00:00'),('2022-02-18T18:00:00'),('2022-02-19T06:00:00'),('2022-02-19T12:00:00'),('2022-02-19T18:00:00'),('2022-02-20T06:00:00'),('2022-02-20T12:00:00'),('2022-02-20T18:00:00'),('2022-02-21T06:00:00'),('2022-02-21T12:00:00'),('2022-02-21T18:00:00'),('2022-02-22T06:00:00'),('2022-02-22T12:00:00'),('2022-02-22T18:00:00'),('2022-02-23T06:00:00'),('2022-02-23T12:00:00'),('2022-02-23T18:00:00'),('2022-02-24T06:00:00'),('2022-02-24T12:00:00'),('2022-02-24T18:00 4 rows affected
 hidden batch(es)