В чем разница между TRUNCATE и DELETE в SQL?

Я написал ответ на этот вопрос по ошибке в ответ на вопрос о разнице между DROP и TRUNCATE, но я подумал, что стыдно не делиться, поэтому я опубликую свой собственный ответ на свой вопрос … даже этические? 🙂

Изменить. Если ваш ответ специфичен для платформы, можете ли вы указать это.

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


Общий обзор

Если вы хотите быстро удалить все строки из таблицы, и вы действительно уверены, что хотите это сделать, и у вас нет внешних ключей к таблицам, тогда TRUNCATE, вероятно, будет быстрее, чем DELETE ,

Необходимо учитывать различные системные проблемы, как описано ниже.


Тип заявки

Удалить – DML, Truncate – DDL


Commit and Rollback

Переменная от поставщика

SQL * Сервер

Усекать можно откат.

PostgreSQL

Усекать можно откат.

оракул

Поскольку TRUNCATE является DDL, он включает в себя два коммита, один до и один после выполнения инструкции. Таким образом, Truncate не может быть откат, а сбой в процессе усечения в любом случае выдает фиксацию.

Однако см. Flashback ниже.


Мелиорация

Удалить не восстанавливает пространство, Truncate восстанавливает пространство

оракул

Если вы используете предложение REUSE STORAGE, тогда сегменты данных не будут выделены, что может быть незначительно более эффективным, если таблица должна быть перезагружена данными. Значок с высоким уровнем воды сбрасывается.


Объем строки

Удалить может удалить только несколько строк. Truncate удаляет все строки.

оракул

Когда таблица разделена на разделы, отдельные разделы могут быть усечены изолированно, поэтому возможно частичное удаление всех данных таблицы.


Типы объектов

Удалить можно применить к таблицам и таблицам внутри кластера. Truncate применяется только к таблицам или всему кластеру. (Может быть специфичным для Oracle)


Идентификатор объекта данных

оракул

Удалить не влияет на идентификатор объекта данных, но truncate присваивает новый идентификатор объекта данных, если никогда не было вставки против таблицы с момента его создания. Даже одна вставка, которая откатна, приведет к присвоению нового идентификатора объекта данных после усечения ,


Flashback (Oracle)

Flashback работает во всех удалениях, но усечение предотвращает возврат флэшбэков до операции.

Однако из 11gR2 функция FLASHBACK ARCHIVE позволяет это, за исключением Express Edition

Использование FLASHBACK в Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638


привилегии

переменная

оракул

Удалить может быть предоставлено в таблице другому пользователю или роли, но усечение не может быть без использования гранта DROP ANY TABLE.


Redo / Undo

Удалить генерирует небольшое количество повторов и большое количество отмены. Truncate генерирует незначительное количество каждого.


Индексы

оракул

Операция truncate снова возвращает неиспользуемые индексы. Удалить нет.


Иностранные ключи

Усечение не может применяться, когда включенный внешний ключ ссылается на таблицу. Обработка удаления зависит от конфигурации внешних ключей.


Блокировка стола

оракул

Truncate требует эксклюзивной блокировки таблицы, для удаления требуется блокировка общей таблицы. Следовательно, отключение блокировки таблиц – это способ предотвращения операций усечения в таблице.


Триггеры

Триггеры DML не срабатывают при усечении.

оракул

Доступны триггеры DDL.


Удаленное выполнение

оракул

Truncate не может быть выдан по ссылке базы данных.


Столбцы идентичности

SQL * Сервер

Truncate сбрасывает последовательность для типов столбцов IDENTITY, а удаление – нет.


Набор результатов

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

например, в подпрограмме Oracle PL / SQL вы можете:

 DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; 

Разница между усечением и удалением приведена ниже:

 +----------------------------------------+----------------------------------------------+ | Truncate | Delete | +----------------------------------------+----------------------------------------------+ | We can't Rollback after performing | We can Rollback after delete. | | Truncate. | | | | | | Example: | Example: | | BEGIN TRAN | BEGIN TRAN | | TRUNCATE TABLE tranTest | DELETE FROM tranTest | | SELECT * FROM tranTest | SELECT * FROM tranTest | | ROLLBACK | ROLLBACK | | SELECT * FROM tranTest | SELECT * FROM tranTest | +----------------------------------------+----------------------------------------------+ | Truncate reset identity of table. | Delete does not reset identity of table. | +----------------------------------------+----------------------------------------------+ | It locks the entire table. | It locks the table row. | +----------------------------------------+----------------------------------------------+ | Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) | | command. | command. | +----------------------------------------+----------------------------------------------+ | We can't use WHERE clause with it. | We can use WHERE to filter data to delete. | +----------------------------------------+----------------------------------------------+ | Trigger is not fired while truncate. | Trigger is fired. | +----------------------------------------+----------------------------------------------+ | Syntax : | Syntax : | | 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name | | | 2) DELETE FROM table_name WHERE | | | example_column_id IN (1,2,3) | +----------------------------------------+----------------------------------------------+ 

Все хорошие ответы, на которые я должен добавить:

Поскольку TRUNCATE TABLE является DDL ( Data Defination Language ), а не командой DML ( Data Manipulation Langauge ), Delete Triggers не запускаются.

DROP

Команда DROP удаляет таблицу из базы данных. Строки, индексы и привилегии всех таблиц также будут удалены. Триггеры DML не будут запущены. Операцию нельзя отменить.

TRUNCATE

TRUNCATE удаляет все строки из таблицы. Операцию нельзя отменить, и триггеры не будут запущены. Таким образом, TRUCATE быстрее и не использует столько пространства отмены, как DELETE.

УДАЛИТЬ

Команда DELETE используется для удаления строк из таблицы. Предложение WHERE может использоваться только для удаления некоторых строк. Если условие WHERE не указано, все строки будут удалены. После выполнения операции DELETE вам необходимо COMMIT или ROLLBACK транзакцию, чтобы сделать изменение постоянным или отменить его. Обратите внимание, что эта операция приведет к срабатыванию всех триггеров DELETE в таблице.

От: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

С SQL Server или MySQL, если есть PK с автоматическим приращением, truncate сбрасывает счетчик.

«Truncate ничего не записывает». Я бы пошел дальше:

Truncate не выполняется в контексте транзакции.

Преимущество скорости усечения над удалением должно быть очевидным. Это преимущество варьируется от тривиального до огромного, в зависимости от вашей ситуации.

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

Резюме удаления Vs Truncate в SQL-сервере
Для полной статьи перейдите по этой ссылке: http://dotnetmob.com/sql-server-article/delete-vs-truncate-in-sql-server/

введите описание изображения здесь

Взято из статьи dotnet mob: удалите Vs Truncate в SQL Server

Да, DELETE медленнее, TRUNCATE быстрее. Зачем?

DELETE должен читать записи, проверять ограничения, обновлять блок, индексы обновления и генерировать повтор / отмену. Все это требует времени.

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

Это особый подход Oracle, AFAIK.

Если вы случайно удалили все данные из таблицы с помощью Delete / Truncate. Вы можете выполнить отмену транзакции. Восстановите последнюю резервную копию и запустите журнал транзакций до того момента, когда произойдет удаление / усечение.

Следующая информация приведена в блоге :

Во время работы с базой данных мы используем Delete и Truncate, не зная различий между ними. В этой статье мы обсудим разницу между Delete и Truncate в Sql.

Удалить:

  • Удалить – это команда DML.
  • Оператор Delete выполняется с использованием блокировки строк, каждая строка в таблице заблокирована для удаления.
  • Мы можем указать фильтры в where where.
  • Он удаляет указанные данные, если существует условие.
  • Удаляет действия триггера, поскольку операция регистрируется отдельно.
  • Медленно, чем Усекать, потому что он хранит журналы

усекать

  • Truncate – это команда DDL.
  • Таблица усечения всегда блокирует таблицу и страницу, но не каждую строку. При удалении всех данных.
  • Невозможно использовать условие Where.
  • Он удаляет все данные.
  • Таблица Truncate не может активировать триггер, потому что операция не регистрирует отдельные удаления строк.
  • Быстрее в производительности, потому что он не хранит никаких журналов.

Примечание. Удаление и усечение обоих можно отменить при использовании с транзакцией. Если транзакция завершена, значит, это значит, что мы не можем отменить команду «Усечение», но мы можем по-прежнему откатить команду «Удалить» из файлов журнала, так как удаление записи записывает их в файл журнала в случае необходимости откат в будущем из файлов журнала.

Если у вас есть ограничение внешнего ключа, ссылающееся на таблицу, которую вы пытаетесь обрезать, это не сработает, даже если в таблице ссылок нет данных. Это связано с тем, что проверка внешнего ключа выполняется с использованием DDL, а не DML. Это можно обойти, временно отключив внешние ограничения (ключи) внешнего ключа к таблице.

Удалить таблицу – это зарегистрированная операция. Таким образом, удаление каждой строки регистрируется в журнале транзакций, что замедляет работу. Таблица Truncate также удаляет все строки в таблице, но не регистрирует удаление каждой строки, вместо этого регистрирует освобождение страниц данных таблицы, что делает ее более быстрой.

~ Если случайно вы удалили все данные из таблицы, используя Delete / Truncate. Вы можете выполнить отмену транзакции. Восстановите последнюю резервную копию и запустите журнал транзакций до того момента, когда произойдет удаление / усечение.

TRUNCATE – это оператор DDL, тогда как DELETE – это оператор DML. Ниже приведены различия между ними:

  1. Поскольку TRUNCATE является языком DDL ( язык определения данных ), он не требует фиксации, чтобы изменения были постоянными. И именно по этой причине строки, удаленные с помощью truncate, не могут быть отброшены. С другой стороны, DELETE является языком DML ( язык манипулирования данными ), поэтому требует явного фиксации, чтобы сделать его действие постоянным.

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

  3. Строки, удаленные инструкцией TRUNCATE TABLE не могут быть восстановлены, и вы не можете указать предложение where в инструкции TRUNCATE .

  4. Операторы TRUNCATE не запускают триггеры, а не триггер delete в инструкции DELETE

Вот очень хорошая ссылка, относящаяся к теме.

TRUNCATE

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

УДАЛИТЬ

  • удаляет строки по одному и записывает запись в журнал транзакций для каждой удаленной строки.
  • если вы хотите сохранить счетчик идентификаторов, вместо этого используйте DELETE . Если вы хотите удалить определение таблицы и ее данные, используйте оператор DROP TABLE .
  • может использоваться с WHERE или без него
  • активирует триггеры.
  • может быть откат.
  • это команда DML.
  • не сбрасывает личность таблицы.

Примечание: DELETE и TRUNCATE оба могут быть отброшены при окружении TRANSACTION если текущий сеанс не закрыт. Если TRUNCATE написан в редакторе запросов, окруженном TRANSACTION и если сеанс закрыт, его нельзя отменить, но DELETE можно отбросить назад.

В SQL Server 2005 я считаю, что вы можете отменить усечение

УДАЛИТЬ

Команда DELETE используется для удаления строк из таблицы. Предложение WHERE может использоваться только для удаления некоторых строк. Если условие WHERE не указано, все строки будут удалены. После выполнения операции DELETE вам необходимо COMMIT или ROLLBACK транзакцию, чтобы сделать изменение постоянным или отменить его. Обратите внимание, что эта операция приведет к срабатыванию всех триггеров DELETE в таблице.

TRUNCATE

TRUNCATE удаляет все строки из таблицы. Операцию нельзя отменить, и триггеры не будут запущены. Таким образом, TRUCATE быстрее и не использует столько пространства отмены, как DELETE.

DROP

Команда DROP удаляет таблицу из базы данных. Строки, индексы и привилегии всех таблиц также будут удалены. Триггеры DML не будут запущены. Операцию нельзя отменить.


DROP и TRUNCATE являются командами DDL, тогда как DELETE является командой DML. Поэтому операции DELETE можно отменить (отменено), в то время как операции DROP и TRUNCATE не могут быть отброшены.

От: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Небольшая поправка к исходному ответу – delete также генерирует значительные количества повторов (так как отменяется сама по себе защита от повтора). Это видно из вывода autotrace:

 SQL> delete from t1; 10918 rows deleted. Elapsed: 00:00:00.58 Execution Plan ---------------------------------------------------------- 0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1) 1 0 DELETE OF 'T1' 2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1) Statistics ---------------------------------------------------------- 30 recursive calls 12118 db block gets 213 consistent gets 142 physical reads 3975328 redo size 441 bytes sent via SQL*Net to client 537 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 10918 rows processed 

TRUNCATE может быть откат, если он завернут транзакцию.

Пожалуйста, ознакомьтесь с двумя приведенными ниже ссылками и проверьте себя:

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

TRUNCATE vs. DELETE является одним из печально известных вопросов во время интервью с SQL. Просто убедитесь, что вы правильно объяснили это интервьюеру, или это может стоить вам работы. Проблема в том, что мало кто знает, что, скорее всего, они посчитают ответ неправильным, если вы скажете им, что YES Truncate можно отбросить назад.

DELETE Statement: эта команда удаляет только строки из таблицы на основе условия, указанного в предложении where, или удаляет все строки из таблицы, если условие не указано. Но это не освобождает пространство, содержащее таблицу.

Синтаксис оператора SQL DELETE:

DELETE FROM table_name [условие WHERE];

Инструкция TRUNCATE: эта команда используется для удаления всех строк из таблицы и освобождения пространства, содержащего эту таблицу.

УДАЛИТЬ

 DELETE is a DML command DELETE you can rollback Delete = Only Delete- so it can be rolled back In DELETE you can write conditions using WHERE clause Syntax – Delete from [Table] where [Condition] 

TRUNCATE

 TRUNCATE is a DDL command You can't rollback in TRUNCATE, TRUNCATE removes the record permanently Truncate = Delete+Commit -so we can't roll back You can't use conditions(WHERE clause) in TRUNCATE Syntax – Truncate table [Table] 

Для получения дополнительной информации посетите

http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/

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

Вот мой подробный ответ на разницу между DELETE и TRUNCATE в SQL Server

Удалить данные : во-первых, оба могут быть использованы для удаления строк из таблицы.
Но DELETE можно использовать для удаления строк не только из таблицы, но также из VIEW или результата OPENROWSET или OPENQUERY с учетом возможностей поставщика.

FROM FROM : С помощью DELETE вы также можете удалять строки из одной таблицы / view / rowset_function_limited на основе строк из другой таблицы, используя другое предложение FROM. В этом разделе FROM вы также можете написать обычные условия JOIN. Фактически вы можете создать оператор DELETE из оператора SELECT, который не содержит каких-либо агрегатных функций, заменив SELECT на DELETE и удалив имена столбцов.
С TRUNCATE вы не можете этого сделать.

ГДЕ : У TRUNCATE не может быть условий WHERE, но DELETE может. Это означает, что с помощью TRUNCATE вы не можете удалить определенную строку или определенную группу строк. TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE.

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

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

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

Trigger : TRUNCATE не активирует триггеры удаления в таблице. Поэтому вы должны быть очень осторожны при использовании TRUNCATE. Не следует использовать TRUNCATE, если delete Триггер определен в таблице для выполнения некоторых действий по автоматическому очистке или протоколированию при удалении строк.

Столбец Identity : с TRUNCATE, если таблица содержит столбец идентификатора, счетчик для этого столбца сбрасывается на начальное значение, определенное для столбца. Если семя не было определено, используется значение по умолчанию 1. DELETE не сбрасывает счетчик идентификаторов. Поэтому, если вы хотите сохранить счетчик идентичности, вместо этого используйте DELETE.

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

Откат : оператор DELETE может быть откат.
TRUNCATE также может быть откат, если он заключен в блок TRANSACTION, а сеанс не закрыт. Когда сеанс закрыт, вы не сможете отменить TRUNCATE.

Ограничения : оператор DELETE может выйти из строя, если он нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице, с ограничением FOREIGN KEY. Если DELETE удаляет несколько строк, и любая из удаленных строк нарушает триггер или ограничение, оператор отменяется, возвращается ошибка и никакие строки не удаляются.
И если DELETE используется против View, этот вид должен быть обновляемым представлением. TRUNCATE не может использоваться против таблицы, используемой в индексированном представлении.
TRUNCATE не может использоваться против таблицы, на которую ссылается ограничение FOREIGN KEY, если только таблица с внешним ключом не ссылается.

Короче говоря, truncate ничего не записывает (так это намного быстрее, но не может быть отменено), тогда как удаление регистрируется (и может быть частью более крупной транзакции, откат и т. Д.). Если у вас есть данные, которые вам не нужны в таблице в dev, обычно лучше обрезать, поскольку вы не рискуете заполнить журнал транзакций

Самое большое различие заключается в том, что truncate является не зарегистрированной операцией, а delete.

Просто это означает, что в случае сбоя базы данных вы не сможете восстановить данные, на которые работает truncate, но с удалением вы можете.

Подробнее здесь

Большая причина, по которой это удобно, – это когда вам нужно обновить данные в многомиллионной таблице строк, но не хотите ее перестраивать. «Удалить *» будет навсегда, в то время как влияние Truncate на производительность будет незначительным.

Невозможно выполнить DDL через dblink.

Я бы прокомментировал сообщение matthieu, но у меня пока нет репутации …

В MySQL счетчик автоматического приращения сбрасывается с усечением, но не с удалением.

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

и усеченная запись может быть откат, если мы определяем транзакцию в начале, и мы можем восстановить усеченную запись после ее отката. Но не удается восстановить усеченные записи из резервной копии журнала транзакций после совершения усеченной транзакции.

Усечение также может быть отменено здесь exapmle

 begin Tran delete from Employee select * from Employee Rollback select * from Employee 

Усечение и удаление в SQL – это две команды, которые используются для удаления или удаления данных из таблицы. Несмотря на довольно простой характер, команды Sql могут создать массу проблем, пока вы не познакомитесь с деталями перед его использованием. Неправильный выбор команды может привести либо к очень медленному процессу, либо даже к взлому сегмента журнала, если слишком много данных нужно удалить, а журнала недостаточно. Вот почему важно знать, когда использовать команду truncate и delete в SQL, но перед их использованием вы должны знать о различиях между Truncate и Delete и основываться на них, мы должны иметь возможность узнать, когда DELETE является лучшим вариантом для удаления данные или TRUNCATE должны использоваться для очистки таблиц.

См.

Выдавая инструкцию TRUNCATE TABLE, вы поручаете SQL Server удалять каждую запись в таблице без каких-либо протоколирования или обработки транзакций.

УДАЛИТЬ

Когда оператор DELETE выполняется без предложения WHERE, он удалит все строки. Однако, когда вставлена ​​новая запись, значение идентификатора увеличивается с 11 (максимальная идентичность) до 12. Он не сбрасывается, а продолжает увеличиваться. TRUNCATE

Когда выполняется инструкция TRUNCATE, он удалит все строки. Однако, когда вставлена ​​новая запись, значение идентификатора увеличивается с 11 (что является исходным значением). TRUNCATE сбрасывает значение идентичности в исходное начальное значение таблицы.

DROP

DELETE и TRUNCATE оба могут быть отброшены при использовании с TRANSACTION.

Если транзакция завершена, значит COMMITED, то мы не можем отменить команду TRUNCATE, но мы можем по-прежнему откатить команду DELETE из файлов LOG, поскольку DELETE записывает их в файл журнала, если это необходимо для отката в будущем из файлов LOG.