i have a partitioned table and i want to drop old partitions

Question

i have a partitioned table and i would like to drop old partitions from the table that are older than 1 year , i there any command top drop these partitions

Answer 1

drop old partitions from the table that are older than 1 year

How do you define the edge of a partition? Do you mean you have a partition function defined on a data column?

See

DROP PARTITION SCHEME (Transact-SQL)

DROP PARTITION FUNCTION (Transact-SQL)

But before you have to delete the "old" data.

Answer 2

Hi @Aiman Kreidly

You can create a temporary table with the same structure as a partitioned table, switching partitions from the partitioned table to be dropped. Then drop the temporary table.

Just like this:

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE StagingTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO StagingTable ;
GO
DROP TABLE StagingTable ;
GO

For more information about table partitioning, here is a good post that you can refer to.

https://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

thank you for the reply, i have a partition function and partition schema defined on which table partitions are created on daily basis,

now we have a requiremend that we need to drop old data which are older than 1 year, delete command would take too long to complete, im looking for dropping the partitions holding the data

Answer 4

This is one area where SQL Server is absolutely appalling. PostgreSQL gets this right - you create partitions and attach/detach them from the main table. The approaches mentioned work, but it's ridiculous that they are necessary.