SQL Partitioning script, Function and Scheme

/* 
DROP partition SCHEME PS_MIH
DROP partition FUNCTION PF_MIH
*/
--// Script Table Partition by Week/Month
SET NOCOUNT ON
DECLARE
@TableName VARCHAR(128)
, @PartitionBy VARCHAR(128)
, @BoundaryType VARCHAR(15)
, @TimeInterval VARCHAR(15)
, @StartingTime DATETIME
, @NumberOfPartitions INT

DECLARE
@PartionCounter INT
, @tmpBoundary CHAR(19)
, @SQLCMD VARCHAR(MAX)
, @CREATE_SCHEMA VARCHAR(MAX)
, @GOBANANA BIT

SELECT
@TableName = 'MIH'
, @PartitionBy = 'MIHDateCreated'
, @BoundaryType = 'RIGHT'
, @TimeInterval = 'MONTH'
, @StartingTime = '05/01/2010'
, @NumberOfPartitions = 260 --520 --// 5 years
--// BE SURE TO DO DRY RUNS. One known limitation is VARCHAR(MAX) = 8000 char
--// The create partition Schema script is stored in variable
, @GOBANANA = 0

DECLARE @tmpIntervals TABLE(ID INT IDENTITY(1,1), Boundary CHAR(19))
--// Lets get this party started
SELECT
@tmpBoundary = @StartingTime
, @PartionCounter = 1
, @CREATE_SCHEMA = 'CREATE PARTITION SCHEME [PS_'
+ @TableName + '] AS PARTITION [PF_' + @TableName + '] TO ([PRIMARY]'

IF @NumberOfPartitions > 1000
BEGIN
PRINT 'Too many partitions. Limit, for SQL 2005 is 1000 which equates to (8000 Char)'
RETURN
END

WHILE @PartionCounter < @NumberOfPartitions AND @tmpBoundary IS NOT NULL
BEGIN

SELECT
@tmpBoundary = CONVERT(CHAR(19),CASE
WHEN @TimeInterval = 'WEEK' THEN DATEADD(wk,@PartionCounter, @StartingTime)
WHEN @TimeInterval = 'MONTH' THEN DATEADD(mm,@PartionCounter, @StartingTime)
ELSE NULL END ,126) --// Format 2010-01-01T00:00:00

IF @PartionCounter = 1
BEGIN
--// Magic Time
SELECT @SQLCMD = 'CREATE PARTITION FUNCTION [PF_'
+ @TableName + '](DATETIME) AS RANGE RIGHT FOR VALUES(''' + @tmpBoundary + ''')'

PRINT @SQLCMD
IF @GOBANANA = 0 EXEC (@SQLCMD)

END
ELSE
BEGIN
SELECT @SQLCMD = 'ALTER PARTITION FUNCTION [PF_' + @TableName + ']()'
+ ' SPLIT RANGE (''' + CONVERT(CHAR(19),@tmpBoundary, 126) + ''');'

PRINT @SQLCMD
IF @GOBANANA = 0 EXEC (@SQLCMD)
END

SELECT
@CREATE_SCHEMA = @CREATE_SCHEMA + '
,[PRIMARY]'-- + CASE WHEN @NumberOfPartitions - @PartionCounter = 1 THEN '' ELSE ',' END
, @PartionCounter = @PartionCounter + 1
END

IF @PartionCounter = 0
BEGIN
PRINT 'Something not right.'
RETURN
END

SELECT @CREATE_SCHEMA = @CREATE_SCHEMA + ')'
PRINT @CREATE_SCHEMA
IF @GOBANANA = 0 EXEC (@CREATE_SCHEMA)

/*
At this point you now need to apply the partion

1. Drop any existing Clustered Index
2. Transaction recommended
3. Following is tail end of Partion Wizard Script
ALTER TABLE [dbo].[MIH] DROP CONSTRAINT [PK_MIH]

ALTER TABLE [dbo].[MIH] ADD CONSTRAINT [PK_MIH] PRIMARY KEY NONCLUSTERED
(
[MIHID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_MIH_634250676763125000] ON [dbo].[MIH]
(
[MIHDateCreated]
)WITH (SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF) ON [PS_MIH]([MIHDateCreated])


DROP INDEX [ClusteredIndex_on_PS_MIH_634250676763125000] ON [dbo].[MIH] WITH ( ONLINE = OFF )

*/

Comments :

0 comments to “SQL Partitioning script, Function and Scheme”