В чем разница DELETE * FROM table и TRUNCATE table в SQL?

Что проверяют

Для тестировщика это вопрос о подготовке и очистке тестовых данных: понимаете ли вы, что остаётся в базе после каждой команды и какие триггеры сработают. Следом спрашивают, как вы чистите базу между автотестами.

Как отвечать

Сначала поправьте формулировку: DELETE * FROM table в PostgreSQL, MySQL и SQL Server даёт синтаксическую ошибку, звёздочку принимает разве что MS Access. Правильно DELETE FROM table.

Дальше различия:

• условие: DELETE принимает WHERE и удаляет выборочно, TRUNCATE очищает таблицу целиком;
• механика: DELETE идёт по строкам и пишет каждую в журнал, TRUNCATE освобождает страницы данных разом, поэтому на больших таблицах он быстрее на порядки;
• триггеры: строковые триггеры ON DELETE срабатывают только на DELETE. Если аудит или каскадная логика живут в триггерах, TRUNCATE их обойдёт;
• счётчики: в MySQL и SQL Server TRUNCATE сбрасывает автоинкремент, в PostgreSQL только с RESTART IDENTITY, по умолчанию последовательность продолжается;
• внешние ключи: таблицу, на которую ссылаются, MySQL и SQL Server очистить через TRUNCATE не дадут, PostgreSQL потребует CASCADE или очистки связанных таблиц той же командой;
• откат: в PostgreSQL и SQL Server TRUNCATE внутри транзакции откатывается. В MySQL и Oracle это DDL с неявным коммитом, откатить нельзя;
• блокировки: TRUNCATE берёт эксклюзивную блокировку всей таблицы, DELETE блокирует строки.

Для автотестов на PostgreSQL удобен TRUNCATE ... RESTART IDENTITY CASCADE перед набором тестов, а для изоляции отдельного теста часто хватает транзакции с откатом в конце.

Где спотыкаются

Говорят «TRUNCATE нельзя откатить» как общее правило. Это верно для MySQL и Oracle, но не для PostgreSQL и SQL Server.

Не учитывают сброс идентификаторов. Тест, который ждёт id = 1 после очистки, в одной базе проходит, а в другой падает.

Проверяют аудит после TRUNCATE и удивляются пустой таблице логов: строковые триггеры не сработали.