Search

truncate partition of partitioned table by Wim SQL Server

Active

38
0
Sign in
 to vote
Type: Suggestion
ID: 417926
Opened: 2/25/2009 7:48:47 AM
Access Restriction: Public
2
Workaround(s)
To delete all rows of a table we can use truncate table, if we remove all Foreign keysd and put them back on afterwards. This is something very easy, even automatically if a stored procedures has the fixed list of all foreign keys!

To delete all rows of 1 partition of a partitioned table we need to make a dummy table and switch the partition to the other table and then truncate/drop that one.

Wouldn't be easier to allow a TRUNCATE TABLE <table> PARTITION 1 syntax to truncate all rows and to behind the screens the switching!

Now it is very hard to implement a general procedure that can truncate a partition on every possilbe table with the same partitioning function but other columns!
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

Wouldn't be easier to allow a TRUNCATE TABLE <table> PARTITION 1 syntax to truncate all rows and to behind the screens the switching!

Is their an easy way to copy the structure of table including primary keys ans indexes?
Otherwise I have to create a complicated procedure to copy the whole structure to a new table.

Benefits

Faster Development
Improved Reliability
Improved Administration
Improved Performance

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by GHaisty on 11/12/2012 at 3:52 PM
The inability of SQL Server to do partition maintenance without such disruptions keeps it from being a true enterprise database like Oracle. In Oracle, I can maintain rolling partitions without any disruptions to our large-scale (>1,000 txn/s) OLTP application. In SQL Server, I have to STOP our application to get the required locks. Insanity. Microsoft's only recommendation has been to create 15,000 partitions. Perhaps they have unlimited storage, neither I nor my clients do.
Posted by Philip Lewis on 8/5/2011 at 7:31 AM
I vote for "Roji. P. Thomas" style suggestion.

The whole "bait & switch" model is unnecessarily tedious and the fudamental idea seems to be predicated on assumptions that the data is actually going somewhere. Solutions that require dynamically creating and manipulating metadata structures are, I submit, suboptimal.

In my case, I just want to blow the data away as quickly as possible.
Currently I need to delete 55 million rows daily from a table with over 4 billion rows (with growth 10%/month) and multiple indices.

TRUNCATE was invented precisely for this type of scenario (due to the costs involved with DELETEs of large sets).

TRUNCATE TABLE <table> PARTITION <partition_id|partition_name> is a no-brainer extension to the TRUNCATE functionality.

I mean, how hard can it be?


Philip
Posted by Mike C_1 on 11/13/2010 at 2:32 PM
A good business case for this is for large ETL applications where you want to partition the staging tables for performance reasons. Once you've imported the data to the staging table and moved it on to the target tables, it's not necessary to save the staging data. It's common to truncate the staging table. Deleting all rows from a partition or table (via DELETE) can be extremely slow due to extensive logging. Being able to truncate a single partition at a time after processing it would be a very valuable feature.

Also see https://connect.microsoft.com/SQLServer/feedback/details/328093/, a feature whose implementation might be impacted by this feature.
Posted by TJ Simba on 4/19/2010 at 2:16 PM
We have an application that utilises ANSII SQL via JDBC to truncate an Oracle table partition. We now wnat to support SQL Server databases without changing the application logic. How can we truncate a SQL Server table via JDBC and ANSII SQL ?

Thanks,
TJ Simba
Posted by Microsoft on 2/8/2010 at 12:29 PM
Thanks Don, Roji, Vidyadhar, GHepper, and everyone who has voted so far for your feedback. We do pay attention to your input. At this point I cannot promise you that TRUNCATE PARTITION will be in the next release, but we will keep your feedback in mind along with other suggestions and customer feedback as we prioritize all the improvements we would like to make to SQL Server. We appreciate your help in making SQL Server a better product.

Susan Price
SQL Server Program Manager
Posted by ghepper on 2/8/2010 at 11:24 AM
Although my preference is to work with SQL Server... the relatively immature Partition management functionality in SQL Server has proven to be an impediment for large scale adoption of SQL Server for Data Warehousing in our organization. When you are dealing with a 10 billion row table that is heavily partitioned for performance, and need to move 250 million rows in/out per batch run, a DELETE will simply not cut it. This is where a TRUNCATE PARTITION is crucial.

I would recommend that Microsoft more closely review their competitors product features (namely Oracle) and spend more time on closing the gap for crucial and frequently used "mind-numbing" functionality before piling in new features that hardly anyone uses.

Posted by Vidhya_Pande on 2/6/2010 at 12:16 AM
http://www.sqlservercentral.com/scripts/Truncate/69506/

Please have a look at this script.
Posted by Vidhya_Pande on 1/8/2010 at 11:47 AM
I have procedure created for truncating the partition automatically taking table name and partition number as a input.

When you are migrating from Oracle to SQL Server and if oracle procedural code is using alter table ..truncate partition command we need to come up with similar command to migrate the code. Manual steps will not work here.

Please drop me a e-mail on vidya_pande@hotmail.com
Will respond back with the script for the same.

Vidyadhar..
Database Architect


Posted by Roji. P. Thomas on 3/26/2009 at 12:10 AM
The current solution with SWITCH partion to a table with the same schema is an Overkill in scenarions where you jsut want to delete and probably repopulate rows for a specific partition.

Support to some syntax like

TRUNCATE TABLE <table> PARTITION <constant|variable>

will be great.
Posted by Microsoft on 3/23/2009 at 1:49 PM
Hello,

Have you tried using the Manage Partition Wizard? (Right click on the table name in Object Explorer, select Storage -> Manage Partition). The partition wizard will script a staging table for you, including creating the indexes. You can either just create the script, or have it create the script and run it immediately. Then all you have to do is switch out the partition and drop the staging the table.

Thanks for your feedback,

Susan Price
SQL Server Program Manager
Posted by Don Vilen on 3/19/2009 at 7:21 AM
My customer uses the partitioning functionality extensively to remove data older than a certain date, either whole days or whole months at a time. There's not always a need to archive the data, so the switching-out of the partition into some staging table is more than is needed. Simply truncating the partition (provided all indexes are aligned, etc.) helps by removing the requirement that an identical staging table has to be maintained or created simply for the truncation. Alternatively a "Create Table As Clone Of" that clones a current table for use as a staging table would be helpful. Otherwise the staging table has to always be maintained to have the same indexes, etc., as the actual table. Creating it when needed and dropping it when finished could be a simpler solution.
Posted by Microsoft on 2/27/2009 at 10:16 AM
Hello,

Thank you for contributing this suggestion. The switch partition functionality was designed to facilitate moving data from an older partition into a new table that could be archived, possibly on cheaper media. However, if you have no need to archive the data, I can see where it could be useful to simply truncate the partition.

I will look into the feasibility of including the ability to truncate a partition in a future release.

Susan Price
SQL Server Program Manager
Sign in to post a workaround.
Posted by Vidhya_Pande on 2/6/2010 at 12:16 AM
http://www.sqlservercentral.com/scripts/Truncate/69506/

Please try with this script
Posted by Vidhya_Pande on 1/8/2010 at 11:50 AM
I have procedure created for truncating the partition automatically taking table name and partition number as a input.

When you are migrating from Oracle to SQL Server and if oracle procedural code is using alter table ..truncate partition command we need to come up with similar command to migrate the code. Manual steps will not work here.

Please drop me a e-mail on vidya_pande@hotmail.com
Will respond back with the script for the same.

Vidyadhar..
Database Architect



© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement