Início / dba / Perguntas / 207054
Accepted
l.lijith
Asked: 2018-05-18 05:41:33 +0800 CST

Excluir dados de uma partição específica no MS SQL 2012

  • 772

Gostaria de excluir dados de uma partição específica usando o ID da partição. Recebi consultas para truncar dados de partição específica para SQL 2016, mas não encontrei nenhuma consulta para versões inferiores.

Eu tentei a consulta abaixo para excluir apenas dados de partições com ID de partição 14 e 15.

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

A instrução delete removerá a própria partição junto com os dados?

Quero manter a partição intacta e remover dados de partições específicas. Digamos, criei partições em uma tabela, ela tem 5 partições (1,2,3,4,5) e quero remover dados apenas da 2ª e 3ª partição.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Essa instrução de exclusão é uma sintaxe inválida do SQL Server 2012, portanto, não fará nada além de gerar um erro.

    A remoção de uma partição e seu limite associado é feita com MERGEDDL; Instruções DML como DELETEnunca remover partições.

    A maneira mais eficiente de remover todas as linhas de uma partição no SQL 2014 e em versões anteriores é com SWITCH(o SQL 2016 permite que partições específicas sejam especificadas com um TRUNCATE). Para usar SWITCH, crie uma tabela de preparo particionada de maneira semelhante com esquema e índices idênticos. Você pode então alternar dados entre tabelas como uma operação de metadados e, em seguida, TRUNCATEa tabela de preparo.

    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;
    • 8

  2. Esta resposta vem de Cathrine Wilhemsen , a quem eu recomendo ler e assistir presente se ela estiver em um evento próximo. O código vem principalmente dela, com uma mensagem para Itzik Ben-Gan e sua função GetNums.

    Para resumir, você deseja alternar a partição para uma nova tabela e descartar a tabela. Você ainda terá seus números de partição depois, por favor veja o código abaixo (novamente principalmente de Cathrine, eu alterei um pouco.) como uma demonstração.

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

relate perguntas