Udostępnij za pośrednictwem

TRUNCATE TABLE (Transact-SQL)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse w usłudze Microsoft FabricSQL Database w usłudze Microsoft Fabric

Usuwa wszystkie wiersze z tabeli lub określonych partycji tabeli bez rejestrowania pojedynczych operacji usuwania wierszy. TRUNCATE TABLE jest podobna do instrukcji DELETE bez klauzuli WHERE; jednak TRUNCATE TABLE jest szybsza i używa mniejszej liczby zasobów dziennika systemu i transakcji.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database, bazy danych SQL fabric

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

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Składnia usług Microsoft Fabric, Azure Synapse Analytics i Parallel Data Warehouse.

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Argumenty

database_name

Nazwa bazy danych.

schema_name

Nazwa schematu, do którego należy tabela.

table_name

Nazwa tabeli do obcinania lub usuwania wszystkich wierszy. table_name musi być literałem. table_name nie może być funkcją OBJECT_ID() ani zmienną.

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ... n ] ) )

Dotyczy: SQL Server 2016 (13.x) i nowszych wersjach.

Określa partycje, które mają zostać obcięte lub z których wszystkie wiersze są usuwane. Jeśli tabela nie jest partycjonowana, argument WITH PARTITIONS generuje błąd. Jeśli nie podano klauzuli WITH PARTITIONS, cała tabela zostanie obcięta.

<partition_number_expression> można określić w następujący sposób:

  • Podaj liczbę partycji, na przykład: WITH (PARTITIONS (2))

  • Podaj numery partycji dla kilku pojedynczych partycji rozdzielonych przecinkami, na przykład: WITH (PARTITIONS (1, 5))

  • Podaj zarówno zakresy, jak i poszczególne partycje, na przykład: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> można określić jako numery partycji oddzielone wyrazem TO, na przykład: WITH (PARTITIONS (6 TO 8))

Aby obcinać tabelę partycjonowaną, należy wyrównać tabelę i indeksy (partycjonowane w tej samej funkcji partycji).

Uwagi

W porównaniu z instrukcją DELETETRUNCATE TABLE ma następujące zalety:

  • Jest używana mniejsza ilość miejsca w dzienniku transakcji.

    Instrukcja DELETE usuwa wiersze pojedynczo i rejestruje wpis w dzienniku transakcji dla każdego usuniętego wiersza. TRUNCATE TABLE usuwa dane przez cofnięcie przydziału stron danych używanych do przechowywania danych tabeli i rejestruje tylko przydziały strony w dzienniku transakcji.

  • Zwykle jest używana mniejsza liczba blokad.

    Po wykonaniu instrukcji DELETE przy użyciu blokady wiersza każdy wiersz w tabeli jest zablokowany do usunięcia. TRUNCATE TABLE zawsze blokuje tabelę (w tym schemat (SCH-M) i stronę, ale nie każdy wiersz.

  • Bez wyjątku strony zerowe pozostają w tabeli.

    Po wykonaniu instrukcji DELETE tabela może nadal zawierać puste strony. Na przykład nie można cofnąć przydziału pustych stron w stercie bez co najmniej wyłącznego (LCK_M_X) blokady tabeli. Jeśli operacja usuwania nie używa blokady tabeli, tabela (sterta) będzie zawierać wiele pustych stron. W przypadku indeksów operacja usuwania może pozostawić puste strony w tyle, chociaż proces oczyszczania w tle szybko cofa przydział tych stron.

TRUNCATE TABLE usuwa wszystkie wiersze z tabeli, ale struktura tabeli i jej kolumny, ograniczenia, indeksy itd. pozostają. Aby usunąć definicję tabeli oprócz jej danych, użyj instrukcji DROP TABLE.

Jeśli tabela zawiera kolumnę tożsamości, licznik dla tej kolumny zostanie zresetowany do wartości inicjatora zdefiniowanej dla kolumny. Jeśli nie zdefiniowano inicjatora, zostanie użyta wartość domyślna 1. Aby zachować licznik tożsamości, użyj DELETE zamiast tego.

Operację TRUNCATE TABLE można wycofać w ramach transakcji.

W bazie danych SQL sieci szkieletowej obcinanie tabeli powoduje usunięcie wszystkich zdublowanych danych z usługi Fabric OneLake dla tej tabeli.

Ograniczenia

Nie można używać TRUNCATE TABLE w tabelach, które:

  • Są przywoływane przez ograniczenie FOREIGN KEY. Możesz obcinać tabelę z kluczem obcym, który się odwołuje.

  • Weź udział w widoku indeksowanym.

  • Są publikowane przy użyciu replikacji transakcyjnej lub replikacji scalania.

  • Są czasowo wersjonowane przez system.

  • Są przywoływane przez ograniczenie EDGE.

W przypadku tabel z co najmniej jedną z tych cech należy użyć instrukcji DELETE.

TRUNCATE TABLE nie można aktywować wyzwalacza, ponieważ operacja nie rejestruje pojedynczych usunięć wierszy. Aby uzyskać więcej informacji, zobacz CREATE TRIGGER (Transact-SQL).

W usłudze Azure Synapse Analytics and Analytics Platform System (PDW):

  • TRUNCATE TABLE nie jest dozwolone w instrukcji EXPLAIN.

  • TRUNCATE TABLE nie można uruchomić wewnątrz transakcji.

Obcinanie dużych tabel

Program Microsoft SQL Server ma możliwość upuszczania lub obcinania tabel, które mają więcej niż 128 zakresów bez jednoczesnego przechowywania blokad we wszystkich zakresach wymaganych do upuszczania.

Uprawnienia

Minimalne wymagane uprawnienia są ALTER w table_name. TRUNCATE TABLE uprawnienia domyślne do właściciela tabeli, członków stałej roli serwera sysadmin oraz db_owner i db_ddladmin stałych ról bazy danych i nie są przenoszone. Można jednak dołączyć instrukcję TRUNCATE TABLE w module, taką jak procedura składowana, i udzielić odpowiednich uprawnień do modułu przy użyciu klauzuli EXECUTE AS.

Przykłady

A. Obcinanie tabeli

Poniższy przykład usuwa wszystkie dane z tabeli JobCandidate. SELECT instrukcje są uwzględniane przed i po instrukcji TRUNCATE TABLE w celu porównania wyników.

USE AdventureWorks2022;
GO

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;
GO

TRUNCATE TABLE HumanResources.JobCandidate;
GO

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;
GO

B. Obcinanie partycji tabeli

Dotyczy: SQL Server 2016 (13.x) i nowszych wersjach.

Poniższy przykład obcina określone partycje tabeli partycjonowanej. Składnia WITH (PARTITIONS (2, 4, 6 TO 8)) powoduje obcięcie liczb partycji 2, 4, 6, 7 i 8.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. Wycofywanie operacji obcinania

W poniższym przykładzie pokazano, że można wycofać operację TRUNCATE TABLE wewnątrz transakcji.

  1. Utwórz tabelę testową z trzema wierszami.

    USE [tempdb];
GO
CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
GO
INSERT INTO TruncateTest DEFAULT VALUES;
GO 3

  2. Sprawdź dane przed obcięciem.

    SELECT * FROM TruncateTest;
GO

  3. Obcinanie tabeli w ramach transakcji i sprawdzanie liczby wierszy.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Zobaczysz, że tabela jest pusta.

  4. Wycofaj transakcję i sprawdź dane.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Zostaną wyświetlone wszystkie trzy wiersze.

  5. Wyczyść tabelę.

    DROP TABLE TruncateTest;
GO

Powiązana zawartość