December 31, 2011 at 10:54 am
I have a table that contains records of transactions with ID column is primary key
I use partition follow ID column, each partition have 1 million records.
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?
Thanks
December 31, 2011 at 11:03 am
thang_ngo_2002 (12/31/2011)
I have a table that contains records of transactions with ID column is primary keyI use partition follow ID column, each partition have 1 million records.
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?
Thanks
ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, then ALTER TABLE with the same partition scheme.
December 31, 2011 at 5:59 pm
Do you mean clause
ALTER PARTITION FUNCTION ... SPLIT RANGE ( ....);
But I want to add more range, not split
Could you please give me detail SQL command of ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME at that case (I want to add more 5 partitions)
Thanks
December 31, 2011 at 6:55 pm
Yes, I can and I will, if you post or mail me your current partion scheme.
Happy New Year!
December 31, 2011 at 10:13 pm
Yes Sir
It's here
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
Thanks
January 1, 2012 at 2:29 am
Try following commands on Test Server... Once satisfied move it PROD.
ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()
SPLIT RANGE (11000000) -- New Range
GO
ALTER PARTITION SCHEME [psTBLTRANS_ID]
NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition
January 1, 2012 at 9:12 am
You've beaten me to it, Dev - thanks!
January 2, 2012 at 10:08 am
Thanks a lot, it work well
Just funny that when I create SQL command from partition scheme in SQL Studio Management
The sequence of groups is not in correct order
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP12], [GROUP11])
January 15, 2012 at 2:39 pm
I was wondering if you got the answer to your question about adding new partitions w/o splitting the the range ?
How do you add many partitions to an existing table?
Thx
January 15, 2012 at 3:24 pm
You always have to modify the partitioning scheme and ALTER the table. The ALTER statement rejiggles all partitions, whether they already exist or have been just added.
January 16, 2012 at 2:41 am
Dev (1/1/2012)
Try following commands on Test Server... Once satisfied move it PROD.
ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()
SPLIT RANGE (11000000) -- New Range
GO
ALTER PARTITION SCHEME [psTBLTRANS_ID]
NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition
These two commands should be issued in the reverse order: set the NEXT USED partition before performing the SPLIT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 16, 2012 at 9:22 am
dao-434438 (1/15/2012) Via Private Message
Thanks for your reply,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?
thx
===========================================================================
Try following commands on Test Server... Once satisfied move it PROD.
ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()
SPLIT RANGE (11000000) -- New Range
GO
ALTER PARTITION SCHEME [psTBLTRANS_ID]
NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 16, 2012 at 9:50 am
You can ALTER (redefine)( the function and the scheme in one shot, and then use the updated scheme to ALTER table.
Chances are it will be faster than modifying the function and schema in single shots. (Your individual mileage may vary.)
January 16, 2012 at 9:52 am
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
January 16, 2012 at 1:07 pm
dao-434438 (1/15/2012)
I was wondering if you got the answer to your question about adding new partitions w/o splitting the the range ?How do you add many partitions to an existing table?
Thx
There is no real "adding a partition," it is always splitting it. Remember that the last partition in the scheme contains all data >(= depending on left or right) boundary. So in this case, if 11,000,000 was the highest boundary then the last partition would hold all data >(=) 11,000,000. So, that partition has to be split because it contains all values to the limit of the data type. Make sense?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply