Поделиться через

TRUNCATE TABLE (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)Хранилище в Microsoft FabricБаза данных SQL в предварительной версии Microsoft Fabric

Удаляет все строки в таблице или указанные секции таблицы, не записывая в журнал удаление отдельных строк. TRUNCATE TABLE аналогично инструкции DELETE без WHERE предложения; TRUNCATE TABLE однако быстрее используется меньше системных и журналов транзакций.

Соглашения о синтаксисе Transact-SQL

Syntax

Синтаксис для SQL Server, База данных SQL Azure, базы данных 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>

Синтаксис Для Microsoft Fabric, Azure Synapse Analytics и Параллельного хранилища данных.

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

Arguments

database_name

Имя базы данных.

schema_name

Имя схемы, которой принадлежит таблица.

table_name

Имя таблицы, из которой удаляются все строки. table_name должно быть литералом. table_name не может быть OBJECT_ID() функцией или переменной.

WITH ( СЕКЦИИ ( { <partition_number_expression> | <диапазон> } [ , ... n ] ) )

Область применения: SQL Server 2016 (13.x) и более поздних версий.

Указывает секции для усечения или секции, из которых удаляются все строки. Если таблица не секционирована, WITH PARTITIONS аргумент создает ошибку. WITH PARTITIONS Если предложение не указано, вся таблица усечена.

<partition_number_expression> можно указать одним из следующих способов.

  • Указав номер секции, например WITH (PARTITIONS (2))

  • Указав номера нескольких секций, разделив их запятыми, например WITH (PARTITIONS (1, 5))

  • Указав диапазоны секций и отдельные секции, например WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> можно указать как номера секций, разделенные словом TO, например: WITH (PARTITIONS (6 TO 8))

Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).

Remarks

По сравнению с оператором DELETE имеет TRUNCATE TABLE следующие преимущества:

  • Используется меньший объем журнала транзакций.

    Оператор DELETE удаляет строки по одному за раз и записывает запись в журнал транзакций для каждой удаленной строки. TRUNCATE TABLE Удаляет данные путем освобождения страниц данных, используемых для хранения данных таблицы и индекса, и записывает только расположения страниц в журнале транзакций.

  • Обычно используется меньшее количество блокировок.

    DELETE При выполнении инструкции с помощью блокировки строк каждая строка в таблице блокируется для удаления. TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемыSCH-M () и страницу, но не каждую строку.

  • Без исключения нулевые страницы остаются в таблице или его индексах.

    DELETE После выполнения инструкции таблица по-прежнему может содержать пустые страницы. Например, пустые страницы в куче не могут быть освобождены без по крайней мере монопольной (LCK_M_X) блокировки таблицы. Если операция удаления не использует блокировку таблицы, таблица (куча) может содержать множество пустых страниц. Для индексов оператор может оставить пустые DELETE страницы позади. Затем процесс очистки фона освобождает эти страницы.

TRUNCATE TABLE Удаляет все строки из таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. д. остаются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE.

Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не определено, используется значение 1 по умолчанию. Чтобы сохранить счетчик удостоверений, используйте DELETE вместо него.

Операцию TRUNCATE TABLE можно откатить в рамках транзакции.

В базе данных SQL Fabric усечение таблицы удаляет все зеркальные данные из Fabric OneLake для этой таблицы.

Deferred deallocation

Если таблица, использующая 128 экстентов или больше, усечена, ядро СУБД отложит фактическое размещение сделки страниц и связанные с ними блокировки до тех пор, пока транзакция не будет зафиксирована. Усечение происходит на двух отдельных этапах: логических и физических. На логическом этапе существующие единицы выделения, используемые таблицей и его индексами, помечаются для размещения сделки и блокируются до фиксации транзакции. На физическом этапе фоновый процесс удаляет страницы, помеченные для размещения сделки. Это означает, что пространство, выпущенное TRUNCATE TABLE немедленно, может быть недоступно для новых выделений.

Если включено ускоренное восстановление базы данных , усечение использует отдельные логические и физические этапы независимо от количества экстентов.

Limitations

Нельзя использовать TRUNCATE TABLE в таблицах, которые:

  • Ссылаются на FOREIGN KEY ограничение. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.

  • Таблица является частью индексированного представления.

  • Таблица опубликована с использованием репликации транзакций или репликации слиянием.

  • Это темпоральная таблица с управлением версиями.

  • Ссылаются на EDGE ограничение.

Для таблиц с одной или несколькими этими характеристиками используйте инструкцию DELETE .

TRUNCATE TABLE Не удается активировать триггер, так как операция не регистрирует отдельные удаления строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

В Azure Synapse Analytics and Analytics Platform System (PDW):

  • TRUNCATE TABLE не допускается в инструкции EXPLAIN .

  • TRUNCATE TABLE невозможно выполнить внутри транзакции.

Permissions

Минимально необходимым разрешением является ALTER для table_name. TRUNCATE TABLE разрешения по умолчанию владельцу таблицы, членам sysadmin предопределенных ролей сервера и db_ownerdb_ddladmin предопределяемым ролям базы данных и не могут передаваться. Тем не менее инструкцию TRUNCATE TABLE можно встроить в модуль, например в хранимую процедуру, и предоставить соответствующие разрешения этому модулю с помощью предложения EXECUTE AS.

Examples

A. Усечение таблицы

В ходе выполнения следующего примера удаляются все данные таблицы JobCandidate. Инструкции SELECT включены до и после инструкции TRUNCATE TABLE для сравнения результатов.

USE AdventureWorks2022;

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

TRUNCATE TABLE HumanResources.JobCandidate;

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

B. Усечение секций таблицы

Область применения: SQL Server 2016 (13.x) и более поздних версий.

В следующем примере выполняется усечение указанных секций секционированной таблицы. Синтаксис WITH (PARTITIONS (2, 4, 6 TO 8)) задает усечение секций с номерами 2, 4, 6, 7 и 8.

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

C. Откат операции усечения

В следующем примере показано, что TRUNCATE TABLE операция внутри транзакции может быть откатена.

  1. Создайте тестовую таблицу с тремя строками.

    USE [tempdb];

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

  2. Проверьте данные перед усечением.

    SELECT ID FROM TruncateTest;

  3. Усечьте таблицу в транзакции и проверьте количество строк.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT ID FROM TruncateTest;

    Вы видите, что таблица пуста.

  4. Откат транзакции и проверка данных.

    ROLLBACK TRANSACTION;

SELECT ID FROM TruncateTest;

    Вы увидите все три строки.

  5. Очистка таблицы.

    DROP TABLE TruncateTest;

Related content