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

TRUNCATE TABLE (Transact-SQL)

  • Статья

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

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

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

Синтаксис

Синтаксис для 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 }
[ ; ]

Аргументы

database_name

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

schema_name

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

table_name

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

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...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))

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

Замечания

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

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

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

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

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

  • В таблице остается нулевое количество страниц, без исключений.

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

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

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

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

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

Ограничения

Нельзя использовать 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 Невозможно запустить внутри транзакции.

Усечение больших таблиц

В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.

Разрешения

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

Примеры

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

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

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. Усечение секций таблицы

Область применения: 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

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

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

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

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

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

    SELECT * FROM TruncateTest;
GO

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

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

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

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

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

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

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

    DROP TABLE TruncateTest;
GO

Связанный контент