1

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.

  • 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 do not remove partitions. Is your question how to remove all rows from a partition and leave the partition intact or do you want to remove both data and partition? – Dan Guzman May 17 '18 at 13:54
  • 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.@DanGuzman – l.lijith May 17 '18 at 14:00
  • 1
    You can switch a partition to another table, truncate the table and then switch the partition back – Denis Rubashkin May 17 '18 at 14:08
3

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;
  • I like the truncate table vs drop table, keeps it a little more reusable with less overhead of rebuilding it if it needs to be a repeatable task. – Shaulinator May 17 '18 at 14:18
0

This answer comes from Cathrine Wilhemsen whom I highly recommend reading and watching present if she happens to be at an event nearby. The code comes from her primarily, with a shoutout to Itzik Ben-Gan and his function GetNums.

To summarize, you want to switch the partition to a new table and drop the table. You will still have your partition numbers afterwards, please see the below code (again primarily from Cathrine, I altered it slightly.) as a demonstration.

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);

-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);

 IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
             FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;
GO
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(1,1000) dates
CROSS JOIN GetNums(1,1000) AS qty;

-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];

-- Verify row count before switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('Salessource')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partition 2 and 269000 in 3 and 0 in 4
SELECT COUNT(*) FROM SalesTarget; -- 0 rows

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partition 2 and 269000 in 3 and 0 in 4
SELECT COUNT(*) FROM SalesTarget; -- 366000 rows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.