How to Delete Data from a Specific Partition in MS SQL 2012

partitioningperformancequery-performancesql serversql-server-2012

I would like to delete data from a specific partition using the partition ID. I got queries to truncate data from specific partition for SQL 2016 but did not find any query for lower versions.

I tried below query to delete only data from partitions with partition id 14 and 15.

delete from  partitiontable1 WITH (PARTITIONS (14 to 15))

Will the delete statement remove the partition itself along with data?

I want to keep the partition intact and remove data from specific partitions. Say, I have created partitions on a table, it has 5 partitions (1,2,3,4,5) and I want to remove data only from 2nd and 3rd partition.

Best Answer

This delete statement is invalid SQL Server 2012 syntax so it will do nothing but raise an error.

Removing a partition and it's associated boundary is done with MERGE DDL; DML statements like DELETE never remove partitions.

The most efficient way to remove all rows from a partition in SQL 2014 and earlier versions is with SWITCH (SQL 2016 allows specific partitions to be specified with a TRUNCATE). To use SWITCH, create a similarly partitioned staging table with identical schema and indexes. You can then switch data between tables as a meta-data operation and then TRUNCATE the staging table.

ALTER TABLE dbo.YourTable
    SWITCH PARTITION 1 TO dbo.YourTable_Staging PARTITION 1;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 2 TO dbo.YourTable_Staging PARTITION 2;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 3 TO dbo.YourTable_Staging PARTITION 3;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 4 TO dbo.YourTable_Staging PARTITION 4;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 5 TO dbo.YourTable_Staging PARTITION 5;
TRUNCATE TABLE dbo.YourTable_Staging;

Related Solutions

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+

Sql-server – How to remove a table partition

Rebuild the indexes on the table to a filegroup instead of a partition schema.

Do so in the following order:

CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on the cluster index
CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on all non clustered indexes (one at a time)

If you can take some downtime, consider scripting and dropping the non-clustered indexes first, rebuild the cluster index and then reapply the non-clustered indexes (targeted at the filegroup, not the partition schema of course)

This is faster than merging the partitions together because it can run at high concurrency using the SQL Server index build engine.

With this trick, the schema remains in place, no need to move it all to another table. Depending on your SQL Server edition, you may even be able to do this online.

Best Answer

Related Solutions

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

Sql-server – How to remove a table partition

Related Question