Инструкция TRUNCATE TABLE в Transact-SQL

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

Если у Вас есть таблица в БД, данные которой Вы (или пользователи) периодически полностью удаляете с помощью инструкции DELETE, то данный материал будет Вам интересен, так как в нем мы рассмотрим альтернативную возможность удаления данных, а именно инструкцию TRUNCATE TABLE, которая в некоторых случаях будет предпочтительней, чем DELETE.

Что такое TRUNCATE TABLE?

TRUNCATE TABLE – это SQL инструкция в языке Transact-SQL, которая удаляет все строки в таблице, не записывая в журнал транзакций удаление отдельных строк данных. TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, но она выполняется быстрее и требует меньше ресурсов.

Преимущества TRUNCATE TABLE и отличия от DELETE

• TRUNCATE TABLE требуется меньший объем журнала транзакций, так как она в отличие от инструкции DELETE не заносит в журнал транзакций запись для каждой удаляемой строки, напомню что DELETE производит удаление по одной строке отражая все действия в журнале транзакций;
• TRUNCATE TABLE выполняется быстрее по сравнению с инструкцией DELETE также за счет меньшего использования журнала транзакций;
• Используется меньшее количество блокировок. Инструкция TRUNCATE TABLE в отличие от инструкции DELETE блокирует таблицу и страницу, а не каждую строку таблицы;
• Если инструкция TRUNCATE TABLE применяется к таблице, которая содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения. Инструкция DELETE не сбрасывает счетчик столбца идентификаторов;
• Инструкция TRUNCATE TABLE не активирует триггер, поскольку она не записывает в журнал удаление отдельных строк.

Примечание! Преимущества TRUNCATE TABLE в некоторых случаях, как Вы понимаете, могут быть и недостатками.

Инструкция TRUNCATE TABLE удаляет все строки (данные) таблицы, но структура таблицы: столбцы, ограничения, индексы и так далее сохраняются. Для того чтобы полностью удалить таблицу и ее определение, следует использовать инструкцию DROP TABLE.

Ограничения TRUNCATE TABLE

• Если на таблицу ссылается ограничение FOREIGN KEY, то TRUNCATE TABLE использовать нельзя;
• Если таблица является частью индексированного представления, то TRUNCATE TABLE также нельзя использовать;
• Если таблица опубликована с использованием репликации транзакций или репликации слиянием, то инструкцию TRUNCATE TABLE использовать нельзя.

Пример использования инструкции TRUNCATE TABLE

Для примера давайте создадим таблицу со столбцом идентификаторов, затем добавим в нее данные и удалим их инструкцией TRUNCATE TABLE, затем снова добавим данные и проверим, сбросился ли счетчик столбца идентификаторов на начальное значение.

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.

   
        --Создаем тестовую таблицу
        CREATE TABLE TestTable(
                ProductId INT IDENTITY(1,1) NOT NULL,
                ProductName VARCHAR(100) NOT NULL
        )
        --Добавляем данные
        INSERT INTO TestTable
                VALUES ('Компьютер'),
                           ('Монитор'),
                           ('Принтер')
        --Выборка данных                   
        SELECT * FROM TestTable    
        --Удаляем все данные инструкцией TRUNCATE TABLE
        TRUNCATE TABLE TestTable
        --Снова добавляем данные
        INSERT INTO TestTable
                VALUES ('Компьютер'),
                           ('Монитор'),
                           ('Принтер')
        --Выборка данных
        SELECT * FROM TestTable 

Как видим, счетчик сброшен, инструкция TRUNCATE TABLE отработала.

Заметка! Для изучения всех возможностей SQL и языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL для новичков.

На этом у меня все, пока!