January 16, 2012 at 1:53 pm
SQLKnowItAll (1/16/2012)
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?
I think that we are discussing semantics. Yes, every table has at least one partition, and if you partition it, you are technically splitting one or more partitions.
I think that dao's question was whether you can do it without the explicit SPLIT statement. That was the gist of my previous replies: you can alter (redefine) the function and the scheme to the new form and use them to alter the table.
I prefer the latter approach because it makes deployment from TFS easy and allows you to use DIFF on the schema. If you run individual SPLIT statements, you have to repeat exactly the same procedure for every new deployment, and DIFF will no longer give correct results.
January 16, 2012 at 2:26 pm
I think that dao's question was whether you can do it without the explicit SPLIT statement. That was the gist of my previous replies: you can alter (redefine) the function and the scheme to the new form and use them to alter the table.
I prefer the latter approach because it makes deployment from TFS easy and allows you to use DIFF on the schema. If you run individual SPLIT statements, you have to repeat exactly the same procedure for every new deployment, and DIFF will no longer give correct results.
Are you saying to completely redefine the function? I'm pretty sure you cannot do that. It's not like a view where the ALTER statement lets you completely redefine it. You can only split because all of this data is already partitioned on a schema using a function. The only way to change it without splitting is to:
Create a new partitioned table with the desired partition function, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
Create a partitioned clustered index on a heap.
Drop and rebuild an existing partitioned index by using the Transact-SQL CREATE INDEX statement with the DROP EXISTING = ON clause.
Perform a sequence of ALTER PARTITION FUNCTION statements.
Taken from http://msdn.microsoft.com/en-us/library/ms186307.aspx
Am I missing something?
Jared
CE - Microsoft
January 16, 2012 at 3:15 pm
SQLKnowItAll (1/16/2012)
I think that dao's question was whether you can do it without the explicit SPLIT statement. That was the gist of my previous replies: you can alter (redefine) the function and the scheme to the new form and use them to alter the table.
I prefer the latter approach because it makes deployment from TFS easy and allows you to use DIFF on the schema. If you run individual SPLIT statements, you have to repeat exactly the same procedure for every new deployment, and DIFF will no longer give correct results.
Are you saying to completely redefine the function? I'm pretty sure you cannot do that. It's not like a view where the ALTER statement lets you completely redefine it. You can only split because all of this data is already partitioned on a schema using a function. The only way to change it without splitting is to:
Create a new partitioned table with the desired partition function, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
Create a partitioned clustered index on a heap.
Drop and rebuild an existing partitioned index by using the Transact-SQL CREATE INDEX statement with the DROP EXISTING = ON clause.
Perform a sequence of ALTER PARTITION FUNCTION statements.
Taken from http://msdn.microsoft.com/en-us/library/ms186307.aspx
Am I missing something?
Yes, you are right, you have to go via INSERT.
Sorry that my earlier answers were hasty -- we have a snow whiteout over here and I am one of only few guys who made it to the office.
January 16, 2012 at 3:18 pm
Revenant (1/16/2012)
SQLKnowItAll (1/16/2012)
I think that dao's question was whether you can do it without the explicit SPLIT statement. That was the gist of my previous replies: you can alter (redefine) the function and the scheme to the new form and use them to alter the table.
I prefer the latter approach because it makes deployment from TFS easy and allows you to use DIFF on the schema. If you run individual SPLIT statements, you have to repeat exactly the same procedure for every new deployment, and DIFF will no longer give correct results.
Are you saying to completely redefine the function? I'm pretty sure you cannot do that. It's not like a view where the ALTER statement lets you completely redefine it. You can only split because all of this data is already partitioned on a schema using a function. The only way to change it without splitting is to:
Create a new partitioned table with the desired partition function, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
Create a partitioned clustered index on a heap.
Drop and rebuild an existing partitioned index by using the Transact-SQL CREATE INDEX statement with the DROP EXISTING = ON clause.
Perform a sequence of ALTER PARTITION FUNCTION statements.
Taken from http://msdn.microsoft.com/en-us/library/ms186307.aspx
Am I missing something?
Yes, you are right, you have to go via INSERT.
Sorry that my earlier answers were hasty -- we have a snow whiteout over here and I am one of only few guys who made it to the office.
🙂 We had that on Friday. Just wanted to make sure my understanding was correct.
Jared
CE - Microsoft
March 16, 2012 at 2:54 am
Hi Revenant,
I also have to add more partitions to an existing partition function. I got the bit what Dev said. I can't understand what do you mean by ALTER table.
Could you please elaborate?
Regards,
March 25, 2013 at 1:55 pm
would it make any complication if I need to add new partition(split last partition) and already millions of records exist on last partition? exp. I have Jan, Feb partition and want to add March but there are millions of records already exist for March range.
September 20, 2013 at 2:08 pm
If you are running out of partitions and the last partition is getting all rows. The question is without splitting the last partition how can we add more partitions to the existing table without affecting resources.
Thanks,
Sree
January 13, 2015 at 4:57 am
It could be the reason your current FG usage is GROUP10. Kindly verify this.
April 22, 2015 at 6:56 am
Its probably a bad idea to have any data going into your last partition. We always leave an empty partition at the end for this reason. Its a best practice to spit the last partition before you add data to it. Otherwise your partitions will be out of order (assuming you are using range right)
January 11, 2018 at 11:57 am
Dev - Sunday, January 1, 2012 2:29 AMTry following commands on Test Server... Once satisfied move it PROD.ALTER PARTITION FUNCTION [pfTBLTRANS_ID]() SPLIT RANGE (11000000) -- New RangeGOALTER PARTITION SCHEME [psTBLTRANS_ID] NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition
How can I extend upto 2 million in the same format of one file group per one hundred thousand ? Will I be able to add 9 more partitions for the future or I can just add one at a time ?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply