Плюсы и минусы TRUNCATE против DELETE FROM
Может ли кто-нибудь дать мне краткий обзор плюсов и минусов использования следующих двух утверждений:
TRUNCATE TABLE dbo.MyTable
против
DELETE FROM dbo.MyTable
Кажется, что они оба делают одно и то же, когда все сказано и сделано; но должны ли быть различия между ними.
12 ответов
TRUNCATE не генерирует никаких данных отката, что делает его молниеносным. Он просто освобождает страницы данных, используемые таблицей.
Однако, если вы находитесь в транзакции и хотите иметь возможность "отменить" это удаление, вам необходимо использовать DELETE FROM, что дает возможность отката.
РЕДАКТИРОВАТЬ: Обратите внимание, что вышеупомянутое неверно для SQL Server (но это относится к Oracle). В SQL Server можно выполнить откат операции усечения, если вы находитесь внутри транзакции и транзакция не была зафиксирована. С точки зрения SQL Server, одно ключевое отличие между DELETE FROM и TRUNCATE заключается в следующем: "Оператор DELETE удаляет строки по одной за раз и записывает запись в журнал транзакций для каждой удаленной строки. TRUNCATE TABLE удаляет данные, освобождая страницы данных используется для хранения данных таблицы и записи только освобождения страниц в журнале транзакций."
Другими словами, во время TRUNCATE меньше регистрируется, потому что в журнале транзакций записываются только освобождения страниц, тогда как при УДАЛЕНИИ ОТ каждой записи записывается удаление. Это одна из причин, почему TRUNCATE молниеносно.
Обратите внимание также на эту ссылку MSDN, что вы не можете усекать таблицы, на которые ссылаются ограничения внешнего ключа, участвовать в индексированном представлении или опубликованы с использованием репликации транзакций или репликации слиянием.
РЕДАКТИРОВАТЬ 2: Другой ключевой момент заключается в том, что TRUNCATE TABLE сбрасывает вашу идентичность на начальное начальное значение, тогда как DELETE FROM будет продолжать увеличиваться с того места, на котором остановился. Справка: ответ Бена Робинсона.
Другим ключевым моментом, не упомянутым в других ответах, является то, что TRUNCATE TABLE сбросит вашу личность на начальное семя, тогда как DELETE FROM будет продолжать увеличиваться с того места, где остановился.
Другое отличие с точки зрения безопасности состоит в том, что TRUNCATE требует привилегий ALTER для таблицы, в то время как DELETE просто требует (барабанит) разрешения DELETE для этой таблицы.
TRUNCATE TABLE не регистрирует транзакцию. Это означает, что это молниеносно для больших столов. Недостатком является то, что вы не можете отменить операцию.
DELETE FROM регистрирует каждую строку, которая удаляется в журналах транзакций, поэтому операция занимает некоторое время и приводит к значительному росту ваших журналов транзакций. Плюс в том, что вы можете отменить операцию, если это будет необходимо.
План удаления и усечения в SQL-сервере
Для полной статьи возьмите после этого подключения: Удалить Vs Truncate в SQL Server
/*Truncate - Syntax*/
TRUNCATE TABLE table_name
/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition
Я полагаю, что Delete и Truncate можно откатить, только если операция была выполнена в явной транзакции. В противном случае вам придется выполнить восстановление, чтобы восстановить удаленные данные
Принципиальное отличие заключается в том, как они регистрируются. DELETE и TRUNCATE регистрируются по-разному, но оба могут быть откатаны одинаково. Все операции, которые изменяют данные, регистрируются. В SQL Server нет такой вещи, как незарегистрированная операция.
Плюс все ответы, еще один момент, который нужно учитывать, что Truncate не вызовет delete trigger стола, но delete заявление вызовет delete trigger таблицы для каждой строки.
Одна вещь, которая очень важна (IMO) и не упоминается в других ответах, это то, что TRUNCATE нужна схема стабильности блокировки, Sch-S, в то время как DELETE использует блокировки строк. Давайте проверим следующее:
BEGIN TRANSACTION;
BEGIN TRY
-- Truncate below will take LCK_M_SCH_S lock for TABLE_A
TRUNCATE TABLE TABLE_A
-- Lets say the query below takes 5 hours to execute
INSERT INTO
TABLE_A
SELECT
*
FROM
GIANT_TABLE (NOLOCK)
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
Теперь предположим, что через 1-2 минуты после начала этого запроса, скажем, мы попытались выполнить следующее:
SELECT COUNT(*) FROM TABLE_A (NOLOCK)
Обратите внимание, что я использовал NOLOCK пункт. Как вы думаете, что произойдет сейчас? Этот запрос будет ждать 5 часов. Зачем? Так как NOLOCK пункт о потребностях Sch-S заблокировать TABLE_A но это TRUNCATE пункт имеет Sch-S на это уже. Поскольку мы еще не зафиксировали транзакцию, блокировка включена даже после этого TRUNCATE пункт. Sch-S Блокировка на столе в основном означает, что либо TABLE_A изменяется, добавляя / удаляя столбцы и т. д., или усекается. Вы даже не можете выполнить что-то вроде ниже:
SELECT object_id('TABLE_A')
Это тоже застрянет на 5 часов. Однако, если вы замените это TRUNCATE с DELETE FROMвы увидите, что не будет Sch-S заблокируйте таблицу и приведенные выше запросы не будут зависать.
Еще одно различие между DELETE против TRUNCATE это поведение, когда таблица повреждена.
Например:
DELETE FROM table_name;
В конечном итоге с ошибкой:
Сообщение 3314, уровень 21, состояние 3, строка 1
Во время отмены зарегистрированной операции в базе данных "..." произошла ошибка с идентификатором записи журнала (). Как правило, конкретный сбой регистрируется ранее как ошибка в службе журнала событий Windows. Восстановите базу данных или файл из резервной копии или восстановите базу данных.
Сообщение 0, Уровень 20, Состояние 0, Строка 0
Произошла серьезная ошибка в текущей команде. Результаты, если таковые имеются, должны быть отброшены.
В то время как TRUNCATE буду работать:
TRUNCATE TABLE table_name;
-- Command(s) completed successfully.
$connection = $this->getEntityManager()->getConnection();
$connection->exec("Truncate TABLE <tablename>;");
truncate не ведет никаких журналов, удаление делает, поэтому, если у вас есть тонна записей, ваш транс-журнал огромен