SQL Server Scripting Partitioning

Question

Had a good look on the net and books online and couldn't find an answer to my question, so here goes.

Working on someone else's design, I have several tables all tied to the same partition schema and partition function. I wish to perform a split operation which would affect many hundreds of millions of rows.

To split is no problem:

ALTER PARTITION SCHEME [ps_Scheme] NEXT USED [FG1]  ; 
ALTER PARTITION FUNCTION [pfcn_Function]() SPLIT RANGE (20120331)

However, I'm concerned that this will affect many tables at once and is not desirable.

Therefore, I was going to create a new copy of the table and do the split on a new function

CREATE PARTITION FUNCTION [pfcn_Function1](INT) 
AS RANGE RIGHT 
FOR VALUES 
(
 20090101, 20090130, 20090131, 20090201...etc
)

CREATE PARTITION SCHEME [ps_Scheme1] 
AS PARTITION [pfcn_Function1] TO 
([FG1], [FG2] etc

CREATE TABLE [dbo].[myTableCopy]
(
....
) ON ps_Scheme1

Then I would switch the partition I wish to split across:

-- The partition numbers did not align because they are based on 2 different functions.
ALTER TABLE [Table] SWITCH PARTITION 173 TO [TableCopy] PARTITION 172

Finally my question is can this be automated? You can make a copy of the table easily in SQL using SELECT INTO, but I cannot see how to automate the partitioning of the table i.e. the bit on the end of the CREATE TABLE statement that points to the partition scheme.

Thanks for any responses.

Answer 1

Found this on books online:

You can turn an existing nonpartitioned table into a partitioned table in one of two ways.

One way is to create a partitioned clustered index on the table by using the CREATE INDEX statement. This action is similar to creating a clustered index on any table, because SQL Server essentially drops the table and re-creates it in a clustered index format. If the table already has a partitioned clustered index applied to it, you can drop the index and rebuilding it on a partition scheme by using CREATE INDEX with the DROP EXISTING = ON clause

I think this might solve my problem.

Answer 2

It can be automated, but I'm not sure is worth it. If is only 'several' tables, not hundreds, then is better to just script out each table and then build a script that does the copy out/split the copy/switch out/split the source/switch in.

Automating this would involve dynamically building the temp table definition(s), including all indexes, from sys.tables/sys.columns/sys.indexes/sys.index_columns and other similar views. Same way SMO Scripting does it.

Answer 3

Yes, you can switch partitions in a automated process. Here is a code sample you can customise. It is driven from a metadata table.

CREATE TABLE [dbo].[PartitionTableSetup](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](256) NULL,
[SwitchTable] [varchar](256) NULL,
[Partition] [int] NULL)

select @merge = (
Select N'' + com + '' from (
Select N' ALTER TABLE  '
+ TableName +
' SWITCH PARTITION 2 TO '
+ SwitchTable 
+ ' PARTITION 2 Truncate table ' 
+ SwitchTable as com
,value
,1 as ord
From (
SELECT convert(datetime,value) as value
,pt.TableName
,pt.SwitchTable
FROM sys.partition_range_values AS RV
JOIN sys.partition_functions AS PF 
ON RV.function_id = PF.function_id
Join dbo.[Partitions] pr 
On name = PartitionFunction
Join dbo.PartitionTableSetup pt 
On pt.[Partition] = pr.ID
WHERE datediff(d,convert(datetime,value),GETDATE()) > pr.[Range] -3
) a
Union all
Select N' ALTER PARTITION FUNCTION '
+ b.PartitionFunction 
+ '() MERGE RANGE ('''
+ Convert(nvarchar,value,121) 
+''')' as com
,value
,2 as ord
From (
SELECT convert(datetime,value) as value
,pr.PartitionFunction
FROM sys.partition_range_values AS RV
JOIN sys.partition_functions AS PF 
ON RV.function_id = PF.function_id
Join dbo.[Partitions] pr 
On name = PartitionFunction
WHERE datediff(d,convert(datetime,value),GETDATE()) > pr.[Range] -3
) b       
) c Order by value
, ord
for xml path ('')
)   
EXECUTE (@merge)