SQL Server All in One Partitioning
Following up on my earlier post about partitioning management and how to automate it, this post will cover some steps to convert existing tables to a partitioning structure.
I will include the code below and talk about the relevant sections. We will create a database, add filegroups and files, build partition functions and schemes and manipulate tables and indexes. You can pick and choose which pieces you want to use. If you are working in a current database, for example, you need not create a new database.
Within the code I use an arbitrary column called FiscalDate that represents the date column in your tables. I assume you have a date column in your table and you would like to partition it by date. This column is assumed to be a type of DATETIME.
To create the partition scheme and function, I wrap the below code in a TRY..CATCH and check first if the scheme/function exists before dropping and recreating it. The partition function is range right (whether to use range right or range left depends on business requirements) and includes some placeholder values, which you can edit as needed. I also specify an addition filegroup in the partition scheme as a placeholder for the next value in the partition function.
IF EXISTS ( SELECT name
FROM sys.partition_schemes
WHERE name = 'PS_FiscalDate_Monthly' )
DROP PARTITION SCHEME [PS_FiscalDate_Monthly];
IF EXISTS ( SELECT name
FROM sys.partition_functions
WHERE name = 'PF_FiscalDate_Monthly' )
DROP PARTITION FUNCTION [PF_FiscalDate_Monthly];
CREATE PARTITION FUNCTION [PF_FiscalDate_Monthly](DATETIME) AS RANGE RIGHT FOR VALUES
(N'2016-01-01T00:00:00', N'2016-02-01T00:00:00', N'2016-03-01T00:00:00');
CREATE PARTITION SCHEME [PS_FiscalDate_Monthly] AS PARTITION [PF_FiscalDate_Monthly] TO
([PartitionedData], [PartitionedData], [PartitionedData], [PartitionedData]);
The next task is to drop existing non-unique, non-key indexes. Key indexes will be done later. This code uses a cursor to loop through indexes in metadata. Dynamic SQL is used to actually perform the work. Explicit transactions are used to control consistency and TRY..CATCH logic is used as before. If you want to focus on specific tables in the database, you can define them in a list and specify them in the where cause of the select query for the cursor.
DECLARE @SchemaName VARCHAR(256);
DECLARE @TableName VARCHAR(256);
DECLARE @IndexName VARCHAR(256);
DECLARE @TSQL NVARCHAR(MAX);
DECLARE CursorDropIndexes CURSOR
FOR
SELECT SCHEMA_NAME(t.schema_id) ,
t.name ,
i.name
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0;
OPEN CursorDropIndexes;
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName, @IndexName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL = 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON '
+ QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
PRINT @TSQL;
EXEC sp_executesql @TSQL;
PRINT N'Complete';
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName,
@IndexName;
END;
CLOSE CursorDropIndexes;
DEALLOCATE CursorDropIndexes;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
PRINT N'SUCCESS'
After this is done we can consider any partitioning columns and make sure they do not allow NULLs, as tables that are partitioned on a column do not allow NULL values in that column. The code for this is below. As before, it’s wrapped in a TRY..CATCH and an explicit transaction is used.
DECLARE @ColumnName VARCHAR(256);
DECLARE CursorColumn CURSOR
FOR
SELECT SCHEMA_NAME(t.schema_id) ,
t.name ,
c.name
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name = 'FiscalDate'
AND c.is_nullable = 1;
OPEN CursorColumn;
FETCH NEXT FROM CursorColumn INTO @SchemaName, @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.'
+ QUOTENAME(@TableName) + ' ALTER COLUMN '
+ QUOTENAME(@ColumnName) + ' DATETIME NOT NULL';
PRINT @TSQL;
EXEC sp_executesql @TSQL;
PRINT N'Complete';
FETCH NEXT FROM CursorColumn INTO @SchemaName, @TableName,
@ColumnName;
END;
CLOSE CursorColumn;
DEALLOCATE CursorColumn;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
PRINT N'SUCCESS'
The final task before we add our indexes back is to drop any key indexes. The code for this is very similar to the code above so I’ll let you explore it in the script. Once this is done we can add our indexes. This will be up to you as indexes depend on how you want your table to perform, but I’ve included a sort of placeholder code below. This is how a primary key clustered index specified on the partitioning scheme would be defined.
BEGIN TRY
PRINT N'MyTable';
CREATE TABLE MyTable
(
FiscalDate DATETIME NOT NULL
)
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[FiscalDate] DESC
) ON [PS_FiscalDate_Monthly] (FiscalDate);
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
PRINT N'SUCCESS'
END TRY
To see the full script, please click here.