SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically Truncate a Partitioned Table


Dynamically Truncate a Partitioned Table

Author
Message
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Comments posted to this topic are about the item Dynamically Truncate a Partitioned Table
Sean Woehrle
Sean Woehrle
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 155
Great work. I use the ManagePartition.exe found out on codePlex for this purpose. If you haven't seen it, it might be worth a read for you.
http://sqlpartitionmgmt.codeplex.com/

Cheers,

Sean :-D

Anthony Martin-330188
Anthony Martin-330188
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 72
Great work building your own script, I went down the same path a few years ago until I found ManagePartition.exe. Check out my two part blog series on working with it.

Part 1: Intro to SQL Server Partition Management Utility
Part 2: Using Partition Management Utility with SSIS
Andrew Senior
Andrew Senior
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 102
Good stuff. This should also assume that any other indexes on the table are aligned.
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
The exact list of requirement for partition switch seems very long:

http://msdn.Microsoft.com/en-us/library/ms191160(v=sql.105).aspx

That is exactly one reason I am trying to come up with this sp to hide/deal with these details.

I did few tests and it seems to me that we don't have to have FK to be able to truncate the partition (switch to an empty clone table).

I really hope one day sql server will support partition truncation natively.

As for the index requirement, I did some quick test and you are right: all other indexes have to be aligned. Otherwise we will get an error such as
'ALTER TABLE SWITCH' statement failed. The table 'TableName' is partitioned while index 'IX_TableName_IndexName' is not partitioned.

I want to confirm that if we have other aligned index(es) on the original table, we don't have to create them in the empty clone table and the partition swith will work.
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Hi Anthony,
Thank you so much for your feedback and information.
I did not know PartitionManagement.exe before. It is indeed a very useful tool which should be provided by SSIS team.
I write the procedure (and other one to dynamically add a partition to a table) so that they can be used from anywhere: T-sql or SSIS package. We recently have to deal with table partition and we want to be able to add/truncate partitions dynamically. We have to also come up with a "smart partition key" idea to combine 2 columns into 1 column since SQL server can only partition on one single column.
I really hope one day we can do multiple columns partition with SQL server and we can manage table partitions with ease.
sqlcentral2@mailinator.com
sqlcentral2@mailinator.com
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 9380
Sadly the procedure is broken.

a) It does not correctly respect the schema specified in the call when not dbo, thus crashing
b) It does not respect computed columns in the partitioned table

There are probably other things broken, but 2 bugs in my testing is enough to throw it back to the author.

That being said, this is precisely why explicit partition truncation should be part of the DDL as it is in Oracle and others.

4x4 photos
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Thank you for checking and using this stored procedure.
I noticed issue 1) before and it is a very simple fix. We need to add the schema name in the dynamic script. I will also fix issue 2 and post the changes later.
There is also another limitation: all indexes are aligned with partition.
I agree: partition truncation should be built-in DDL operation but SQL server still does not support it.
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
I have fixed both the schema name and computed column issues and did some quick test. Looks fine now. The new change is posted back (the full script code and attached file are all updated). Also this procedure should work now with both left and right partition type.
Thanks!
sergiu0303
sergiu0303
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 116
Hello Steven,

One other thing, it doesn't work on tables partitioned on columns of any other data type than int. For example, my partition column is of datetime. The script fails right from the start where it verifies if the partition table, schema or partition number exists:
-- need exact match
AND cast(rv.value as int)=@partitionValue

I will modify the script to fit my case, but you can modify the procedure so you can pass the data type of the partitioned column as well.
And btw.... nice job man!! Smile It saved me at least 2 days of figuring it out on myself!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search