Dynamically Truncate a Partitioned Table

Question

Dynamically Truncate a Partitioned Table

stevenzrao

SSC Enthusiast

Points: 131
More actions
July 10, 2013 at 8:27 pm

#279664

Comments posted to this topic are about the item Dynamically Truncate a Partitioned Table

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Woehrle Mr or Mrs. 500 Points: 539 More actions · Answer 1

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/

[font="Tahoma"]Cheers,

Sean :-D[/font]

Anthony Martin-330188 Grasshopper Points: 15 More actions · Answer 2

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 Grasshopper Points: 17 More actions · Answer 3

Good stuff. This should also assume that any other indexes on the table are aligned.

stevenzrao SSC Enthusiast Points: 131 More actions · Answer 4

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 SSC Enthusiast Points: 131 More actions · Answer 5

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 SSCommitted Points: 1514 More actions · Answer 6

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 SSC Enthusiast Points: 131 More actions · Answer 7

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 SSC Enthusiast Points: 131 More actions · Answer 8

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 SSC Veteran Points: 280 More actions · Answer 9

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!! 🙂 It saved me at least 2 days of figuring it out on myself!