Dynamically Truncate a Partitioned Table

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

  • 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]

  • 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

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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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!

  • 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!

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

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