Получить профессию в SkyproРазбираем разницу между TRUNCATE и DELETE в SQL
Быстрый ответ
Команда TRUNCATE выполняет очистку таблицы в массовом порядке и отличается высокой скоростью выполнения. Эта команда удаляет все строки, не позволяя указывать условия, и при этом не регистрирует каждое изменение в журнале. Помимо этого, TRUNCATE сбрасывает автоинкрементные счётчики, если таковые присутствуют в таблице. Используйте TRUNCATE, если вам необходимо быстро и полностью очистить таблицу:
TRUNCATE TABLE table_name; -- Таблица пуста.В отличие от TRUNCATE, команда DELETE предоставляет гораздо большую гибкость: она позволяет выставлять ряд условий (WHERE) для удаления определенных строк, использовать откат операций и отслеживать каждое удаление. Этот вариант подходит для случаев, когда требуется контроль над процессом удаления:
DELETE FROM table_name WHERE condition; -- Удаление с условиями.В сравнении TRUNCATE равносилен крупному инструменту, как молотку, а DELETE — это уже скорее точечный инструмент, аналогичный скальпелю.
Когда использовать TRUNCATE, а когда DELETE
Несмотря на то, что TRUNCATE и DELETE оба применяются для удаления строк из таблицы, они делают это согласно различным подходам. Давайте разберемся в деталях их отличий.
Особенности TRUNCATE:
• TRUNCATE — это команда DDL, быстро и целиком очищающая таблицу.
• Это команда освобождает пространство, занимаемое таблицей, возвращая его сразу же обратно.
• Команда не учитывает отдельные строки и не регистрирует лог операций, тем самым, не занимая пространство журналирования.
• TRUNCATE не активирует триггеры на уровне строк, так как не взаимодействует с ними по отдельности.
• Работает с эксклюзивной блокировкой на протяжении всей операции.
• В случае автоинкрементных полей – обнуляет регистры.
• Обновляет неиспользуемые ранее индексы, делая их готовыми к дальнейшему использованию.
TRUNCATE TABLE Employee; -- Полный кадровый перезапуск.Детали DELETE:
• DELETE — это команда DML, позволяющая удалять строки по одной или все сразу, в зависимости от заданных условий.
• Команда регистрирует лог операций для каждой удаленной строки, обеспечивая полноценное логирование.
• DELETE не затрагивает индексы, они остаются в прежнем виде.
• Команда применяет блокировку на уровне строк, обеспечивая возможность соревновательного выполнения операций.
• Поле с автоинкрементом остается без изменений.
• DELETE активирует DML триггеры, если они имеются.
DELETE FROM Employee WHERE name='John Doe'; -- Увольнение конкретного сотрудника.В ролях: DELETE и TRUNCATE
Посмотрим более тщательно на их характеристики:
Журналы транзакций и обработка данных
TRUNCATE действует более экономично по отношению к журналированию — создает меньше данных для отката и не оказывает существенного влияния на журналы.
TRUNCATE TABLE logs; -- Удаление всей истории событий.Команда DELETE документирует каждое удаление, generируя при этом большие объемы данных для отката и повторного выполнения.
DELETE FROM logs WHERE log_date < CURRENT_DATE; -- Удаление данных за период до актуальной даты.Триггеры и возможность отката
TRUNCATE не активирует DML триггеры, в то время как DELETE вызывает их для каждой удаленной строки.
TRUNCATE TABLE tries; -- Триггеры не активизируются.
DELETE FROM tries; -- Активизация триггеров.После применения TRUNCATE отсутствует возможность восстановить данные до состояния до операции.
Права доступа и ограничения
TRUNCATE можно выполнить, имея только соответствующие привилегии, даже без прав на удаление данных.
GRANT SELECT, TRUNCATE ON employees TO user; -- Очистка таблицы возможна, не требующая прав на удаление.Если таблица участвует во внешнем ключевом ограничении, выполнять TRUNCATE не получится, в отличие от DELETE, которое возможно при соблюдении ключевого условия.
DELETE FROM parent_table WHERE id NOT IN (SELECT FOREIGN_ID FROM child_table); -- Удаление с учетом внешних ключей.
TRUNCATE TABLE parent_table; -- Запрет на выполнение из-за внешних ключей.Особенности SQL Server и таблиц, оптимизированных для работы в памяти
В SQL Server TRUNCATE может сбросить счетчики в столбцах Identity. Такого не произойдет в таблицах, оптимизированных для работы в памяти.
Визуализация
-- Диалог TRUNCATE и DELETE
Truncate: Не помню, что удалял 🧠.
Delete: Я тоже забываю после COMMIT или ROLLBACK.
Truncate: Не могу отменить свои действия 😎.
Delete: Я отменяю все до COMMIT 😉.
Truncate: Надо очистить эту таблицу 🏓.
Delete: Пока ты занят, я активизирую ТРИГГЕРЫ 🔫.Практические соображения
Производительность 💹
TRUNCATE выполняется значительно быстрее DELETE, когда нужно удалить все строки из таблицы, так как он совершает массовую операцию.
Масштабируемость 📈
TRUNCATE предпочтительнее при обработке больших объемов данных из-за его минимальных оверхедов на логирование. Использование DELETE без добавления условий может снижать производительность на больших таблицах.
Конкурентность 🧩
При необходимости минимизации блокировок и обеспечении параллелизма, DELETE получает преимущество за счет его меньшего влияния на работу базы данных.
Восстановление данных и аудит 📚
При строгих требованиях к восстановлению данных и аудиту в пользу команды DELETE говорит ее возможность отслеживания и отката транзакций.