从 MS SQL 2012 中的特定分区删除数据

此删除语句是无效的 SQL Server 2012 语法，因此它只会引发错误。

使用MERGEDDL 删除分区及其关联边界；DML 语句，例如DELETE从不删除分区。

在 SQL 2014 及更早版本中，从分区中删除所有行的最有效方法是使用SWITCH（SQL 2016 允许使用 指定特定分区TRUNCATE）。要使用 SWITCH，请创建具有相同架构和索引的类似分区临时表。然后，您可以在表之间切换数据作为元数据操作，然后TRUNCATE是临时表。

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;

这个答案来自Cathrine Wilhemsen，如果她碰巧参加附近的活动，我强烈建议她阅读和观看礼物。代码主要来自她，特别是对Itzik Ben-Gan和他的函数 GetNums 的大力支持。

总而言之，您希望将分区切换到新表并删除该表。之后你仍然会有你的分区号，请看下面的代码（同样主要来自 Cathrine，我稍微改变了它。）作为演示。

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