Отличия TRUNCATE от DELETE в языке T-SQL

Приветствую всех на сайте Info-Comp.ru! Сегодня я расскажу Вам об отличиях, которые существуют между инструкциями TRUNCATE TABLE и DELETE в языке T-SQL, Вы узнаете, в каких случаях лучше использовать DELETE, а в каких предпочтительней TRUNCATE.

Отличия TRUNCATE от DELETE в языке T-SQL

Для того чтобы удалить данные из таблицы в Microsoft SQL Server можно использовать несколько инструкций, в частности DELETE и TRUNCATE TABLE. Эти операторы похожи, так как их предназначение одно – это удаление строк из таблицы. В связи с этим у многих возникают вопросы, например – а в чем отличие TRUNCATE от DELETE? или Когда лучше использовать TRUNCATE, а когда DELETE? Подобные вопросы задают и на собеседованиях, ведь понимание различий между этими операторами помогает разработчикам SQL эффективно работать с данными, строить приложения и запросы.

Заметка! Сборник статей для изучения языка T-SQL и Microsoft SQL Server – в этом материале я собрал лучшие свои статьи по SQL Server и сгруппировал их по уровню подготовки.

Для начала давайте дадим короткое определение этим двум операторам.

Что такое DELETE?

DELETE – это SQL инструкция языка T-SQL, которая удаляет одну или несколько строк из таблицы или представления в Microsoft SQL Server, при этом эта операция всегда полностью регистрируется в журнале транзакций, т.е. каждая удаленная строка.

Что такое TRUNCATE TABLE?

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

Сравнительная таблица отличий TRUNCATE TABLE и DELETE

Теперь давайте сформируем сравнительную таблицу, в которой отразим все отличия и особенности работы TRUNCATE TABLE и DELETE в языке T-SQL.

DELETE TRUNCATE TABLE
DELETE – это оператор DML (Data Manipulation Language) TRUNCATE TABLE – это оператор DDL (Data Definition Language);
DELETE может удалить часть данных за счет использования WHERE TRUNCATE TABLE удаляет все данные из таблицы, секцию WHERE использовать нельзя
DELETE записывает в журнал транзакций удаление каждой строки TRUNCATE TABLE удаляет все строки в таблице, не записывая при этом удаление отдельных строк данных в журнал транзакций
Для DELETE требуется больший объем журнала транзакций Для TRUNCATE TABLE требуется меньший объем журнала транзакций, за счет того, что она не заносит в журнал запись для каждой удаляемой строки
DELETE выполняется медленнее TRUNCATE TABLE выполняется быстрее также за счет меньшего использования журнала транзакций
DELETE удаляет строки по одной за раз TRUNCATE TABLE удаляет все строки в таблице путем освобождения страниц
DELETE создает блокировку на каждую строку, которую требуется удалить из таблицы TRUNCATE TABLE блокирует в целом таблицу и страницу, чтобы удалить все записи
DELETE сохраняет счетчик идентификаторов и не сбрасывает его до начального значения TRUNCATE TABLE сбрасывает счетчик идентификаторов до начального значения
DELETE может активировать триггер TRUNCATE TABLE не может активировать триггер, поскольку в данном случае в журнал не записываются удаление отдельных строк
DELETE сохраняет статистику объекта и выделенное пространство TRUNCATE TABLE освобождает все страницы данных таблицы, поэтому TRUNCATE удаляет всю статистику и выделенное пространство
DELETE можно использовать, если таблица является частью индексированного представления TRUNCATE TABLE нельзя использовать, если таблица является частью индексированного представления
Чтобы использовать DELETE, необходимы соответствующие права на удаление данных Чтобы использовать TRUNCATE TABLE, у Вас должны быть права на ALTER TABLE, т.е. на изменение таблицы

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

Самым очевидным здесь является то, что если Вам нужно удалить не все данные из таблицы, а только часть, т.е. по условию WHERE, то Вы можете использовать только DELETE.

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

Пример отличия в работе TRUNCATE TABLE от DELETE

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

Создание тестовой таблицы

  
   CREATE TABLE Goods(
      ProductId INT IDENTITY(1,1) NOT NULL,
      ProductName VARCHAR(100) NOT NULL
   );

Выполняем удаление с помощью DELETE

   
   --Добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');
        
   --Выборка данных                   
   SELECT * FROM Goods;
        
   --Удаляем все данные инструкцией DELETE
   DELETE Goods;
        
   --Снова добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');
        
   --Выборка данных
   SELECT * FROM Goods;

Скриншот 1

Выполняем удаление инструкцией TRUNCATE TABLE

   
   --Добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');

   --Выборка данных
   SELECT * FROM Goods;

   --Удаляем все данные инструкцией TRUNCATE TABLE
   TRUNCATE TABLE Goods;

  --Снова добавляем данные
  INSERT INTO Goods
     VALUES ('Компьютер'),
            ('Монитор'),
            ('Принтер');

   --Выборка данных
   SELECT * FROM Goods;

Скриншот 2

Как видите, в первом случае счетчик не сбросился, и нумерация продолжилась, а во втором счетчик начался с самого начала.

Заметка! Для комплексного изучения языка T-SQL рекомендую почитать книгу «Путь программиста T-SQL».

На сегодня это все, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.