SQL Server 2016 wprowadza modyfikację do instrukcji TRUNCATE TABLE, która pozwala czyścić określoną partycję lub zestaw partycji z tabeli, jednocześnie uzyskując zalety instrukcji TRUNCATE TABLE bez usuwania wszystkich danych z tabeli. Ten nowy mechanizm filtru w instrukcji TRUNCATE TABLE jest uzyskiwany za pomocą opcji WITH PARTITIONS () określającej partycję lub zestawy partycji.  

TRUNCATE TABLE [ { database_name .[ schema_name ] . | schema_name . } ]
table_name   [ WITH ( PARTITIONS ( {  | 
 }    [ , ...n ] ) ) ]  
 [ ; ]   ::=    
TO partition_number_expression>  

Przykład użycia:

  • TRUNCATE TABLE dbo.powerSQLPartitionTestTable WITH (PARTITIONS (2)); 
  • TRUNCATE TABLE dbo.powerSQLPartitionTestTable WITH (PARTITIONS (1,3,6)) 
  • TRUNCATE TABLE dbo.powerSQLPartitionTestTable WITH (PARTITIONS (1,2, 4 TO 6)); 
  • TRUNCATE TABLE dbo.powerSQLPartitionTestTable WITH (PARTITIONS (4 TO 6));  

Jak i kiedy używać Truncate table z klauzulą Partition? 

Proces szybkiego archiwizowania / oczyszczania wymaga wyłączenia partycji. Chociaż Switch Partition jest operacją aktualizacji metadanych i nie wymaga przenoszenia fizycznych danych pomiędzy plikami danych, w niektórych przypadkach, jak bazy danych, wymaga wyłącznych blokad, a to powoduje blokowanie tabeli do ładowania i usuwania danych.

Usuwanie danych (usuwanie ale nie archiwizowanie) z określonej partycji lub partycji było żmudnym zadaniem w poprzednich wersjach SQL. Dane spowodowały powolność i zablokowały tabelę, co uniemożliwiło użytkownikom odpowiadanie na tabelę; to również zakończyło się używaniem dużej przestrzeni dziennika transakcji.

Przed SQL 2016, następujące kroki zostały wykonane w celu usunięcia danych lub usunięcia plików z partycji 

  • Utwórz nową tabelę do przełączania z tą samą definicją i indeksem klastrowym, co w tabeli podzielonej na partycje 
  • Przełącz partycję na inną tabelę 
  • Zmień funkcję Partycji i Schemat partycji, aby pozbyć się grupy plików 
  • wykonaj merge 
  • Usuń grupę plików 

 A co z wprowadzeniem nowej komendy TRUNCATE TABLE w tym scenariuszu? Jest to dość płynny i skuteczny sposób usuwania wierszy z tabeli podzielonej na partycje, ponieważ działa on jak normalna operacja Truncate tabeli. 

  • Wykonaj TRUNCATE na tabeli z klauzulą partycji 
  • Wykonaj polecenie MERGE 
  • Usuń pustą grupę plików 

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl