dao-434438 (1/15/2012) Via Private Message
I need to add 20 additional partitions to an existing table , do I have execute those commands 20 times and then Alter the partitioned table to use the Altered PF and PS functions?
The following script demonstrates how this works, read the comments and run each statement one at a time:
USE tempdb
GO
-- Two boundaries = three ranges
CREATE PARTITION FUNCTION PF(integer) AS RANGE RIGHT
FOR VALUES (1000, 5000);
GO
-- All to [PRIMARY] just to make the test easier
CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);
GO
-- Test table
CREATE TABLE dbo.Partitioned
(
IntValue integer NOT NULL,
StringValue varchar(50) NOT NULL,
DateValue date NOT NULL,
CONSTRAINT [PK dbo.Partitioned IntValue]
PRIMARY KEY (IntValue)
ON PS (IntValue)
);
GO
-- Test data
INSERT dbo.Partitioned
(IntValue, StringValue, DateValue)
VALUES
(999, '100', '2012-01-01'),
(1999, '200', '2012-01-02'),
(2999, '300', '2012-01-03'),
(3999, '400', '2012-01-04'),
(4999, '500', '2012-01-05'),
(5999, '600', '2012-01-06');
GO
-- Show the two partiton boundaries and the range values
SELECT
prv.boundary_id,
prv.value
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE
pf.name = N'PF'
ORDER BY
prv.boundary_id;
-- Show the three partitons, and number of rows in each
SELECT
p.partition_number,
p.rows
FROM sys.partitions AS p
WHERE
p.object_id = OBJECT_ID(N'dbo.Partitioned')
ORDER BY
p.partition_number;
-- Show partitions and values another way
SELECT
$PARTITION.PF(p.IntValue) AS partition_id,
p.*
FROM dbo.Partitioned AS p
ORDER BY
p.IntValue;
-- Set the next file group to use for a new partition
ALTER PARTITION SCHEME PS
NEXT USED [PRIMARY];
-- Create a new partition at IntValue = 2000
ALTER PARTITION FUNCTION PF()
SPLIT RANGE (2000);
-- Notice the table has automatically been updated
-- There are now four partitions
SELECT
$PARTITION.PF(p.IntValue) AS partition_id,
*
FROM dbo.Partitioned AS p
ORDER BY
p.IntValue;
-- Perform the remaining splits
ALTER PARTITION SCHEME PS
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF()
SPLIT RANGE (3000);
ALTER PARTITION SCHEME PS
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF()
SPLIT RANGE (4000);
-- Six partitions
SELECT
$PARTITION.PF(p.IntValue) AS partition_id,
*
FROM dbo.Partitioned AS p
ORDER BY
p.IntValue;
-- Five partiton boundaries and ranges
SELECT
prv.boundary_id,
prv.value
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE
pf.name = N'PF'
ORDER BY
prv.boundary_id;
-- Show the six partitons, and number of rows in each
SELECT
p.partition_number,
p.rows
FROM sys.partitions AS p
WHERE p.object_id = OBJECT_ID(N'dbo.Partitioned')
ORDER BY
p.partition_number;
-- Tidy up
DROP TABLE dbo.Partitioned;
DROP PARTITION SCHEME PS;
DROP PARTITION FUNCTION PF;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi