В чём разница между DELITE и TRUNCATE?

Разница между командами DELETE и TRUNCATE в SQL

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

Ключевые отличия

1. Механизм удаления и производительность

• DELETE — это DML (Data Manipulation Language) команда, которая удаляет строки по одной, записывая каждое удаление в журнал транзакций. Это делает операцию медленной, особенно для больших таблиц.

  -- Удаляет строки, удовлетворяющие условию WHERE
  DELETE FROM employees WHERE department_id = 10;
  

• TRUNCATE — это DDL (Data Definition Language) команда. Она деаллоцирует (освобождает) данные на уровне страниц, минуя построчную обработку. Фактически таблица "очищается" мгновенно.

  -- Удаляет ВСЕ строки из таблицы. Условие WHERE использовать НЕЛЬЗЯ.
  TRUNCATE TABLE employees;
  

2. Журналирование и возможность отката (ROLLBACK)

• DELETE: Каждая удаленная строка логируется в журнале транзакций (transaction log). Это позволяет выполнить откат операции (ROLLBACK) в рамках транзакции, если она не была зафиксирована (COMMIT).
• TRUNCATE: Журналируется только факт освобождения страниц, а не каждая отдельная строка. В SQL Server и PostgreSQL TRUNCATE можно откатить в транзакции. Однако в Oracle (если не использовать FLASHBACK) и в некоторых других СУБД в определенных режимах эта операция фиксируется немедленно и не подлежит откату.

3. Условия (WHERE) и триггеры

• DELETE поддерживает предложение WHERE, позволяя удалять данные выборочно.
• TRUNCATE не поддерживает WHERE и всегда удаляет все строки из таблицы. Кроме того, TRUNCATE не активирует триггеры DELETE, так как не является операцией DML.

4. Влияние на идентификаторы (IDENTITY / AUTO_INCREMENT)

• DELETE: При удалении строк счетчик автоинкрементного поля (IDENTITY в SQL Server, AUTO_INCREMENT в MySQL, SEQUENCE в Oracle) НЕ сбрасывается. Следующая вставленная строка получит следующее порядковое значение.
• TRUNCATE: Сбрасывает счетчик автоинкрементного поля до начального значения (обычно до 1). Таблица после TRUNCATE будет вести себя как новая.

5. Требования к правам доступа и блокировки

• DELETE требует права на удаление данных из таблицы (DELETE permission).
• TRUNCATE требует более высоких привилегий, так как это операция DDL (например, ALTER TABLE permission). TRUNCATE обычно накладывает эксклюзивную блокировку на таблицу на очень короткое время, в то время как DELETE может удерживать более мелкие блокировки (на строки или страницы) на продолжительный период, что повышает риск блокировок и взаимоблокировок (deadlocks).

6. Внешние ключи (FOREIGN KEY)

• DELETE можно выполнить на таблице, на которую ссылаются внешние ключи, если это позволяет политика ON DELETE.
• TRUNCATE нельзя выполнить для таблицы, на которую ссылаются FOREIGN KEY другие таблицы (за исключением некоторых СУБД, где это возможно при каскадном удалении или отключении проверок).

Сводная таблица

Практические рекомендации по выбору

• Используйте DELETE, когда вам необходимо:

    *   Удалить **часть данных** (`WHERE department = 'Closed'`).
    *   Реализовать сложную логику удаления с активацией **триггеров**.
    *   Иметь возможность **отката** операции (например, в скриптах миграции).

• Используйте TRUNCATE, когда вам необходимо:

    *   **Быстро очистить** всю таблицу (например, этап в ETL-процессе).
    *   Перезапустить нумерацию автоинкрементных полей.
    *   Освободить место, занимаемое таблицей (в сочетании с `DROP STORAGE` в Oracle или аналогичным).

Важное предупреждение: Обе команды необратимо удаляют данные. Перед выполнением, особенно TRUNCATE, всегда убедитесь в наличии актуальной резервной копии или что операция выполняется в управляемой транзакции с возможностью отката. В продакшн-среде TRUNCATE следует применять с крайней осторожностью из-за ее немедленного и полного воздействия.