Отличия TRUNCATE от DELETE в языке T-SQL
Приветствую всех на сайте Info-Comp.ru! Сегодня я расскажу Вам об отличиях, которые существуют между инструкциями TRUNCATE TABLE и DELETE в языке T-SQL, Вы узнаете, в каких случаях лучше использовать DELETE, а в каких предпочтительней TRUNCATE.
Для того чтобы удалить данные из таблицы в Microsoft SQL Server можно использовать несколько инструкций, в частности DELETE и TRUNCATE TABLE. Эти операторы похожи, так как их предназначение одно – это удаление строк из таблицы. В связи с этим у многих возникают вопросы, например – а в чем отличие TRUNCATE от DELETE? или Когда лучше использовать TRUNCATE, а когда DELETE? Подобные вопросы задают и на собеседованиях, ведь понимание различий между этими операторами помогает разработчикам SQL эффективно работать с данными, строить приложения и запросы.
Заметка! Сборник статей для изучения языка T-SQL и Microsoft SQL Server – в этом материале я собрал лучшие свои статьи по SQL Server и сгруппировал их по уровню подготовки.
Для начала давайте дадим короткое определение этим двум операторам.
Что такое DELETE?
DELETE – это SQL инструкция языка T-SQL, которая удаляет одну или несколько строк из таблицы или представления в Microsoft SQL Server, при этом эта операция всегда полностью регистрируется в журнале транзакций, т.е. каждая удаленная строка.
Что такое TRUNCATE TABLE?
TRUNCATE TABLE – это SQL инструкция языка T-SQL, которая удаляет все строки в таблице, не записывая в журнал транзакций удаление отдельных строк данных. TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, но она выполняется быстрее и требует меньше ресурсов.
Сравнительная таблица отличий TRUNCATE TABLE и DELETE
Теперь давайте сформируем сравнительную таблицу, в которой отразим все отличия и особенности работы TRUNCATE TABLE и DELETE в языке T-SQL.
| DELETE | TRUNCATE TABLE |
| DELETE – это оператор DML (Data Manipulation Language) | TRUNCATE TABLE – это оператор DDL (Data Definition Language); |
| DELETE может удалить часть данных за счет использования WHERE | TRUNCATE TABLE удаляет все данные из таблицы, секцию WHERE использовать нельзя |
| DELETE записывает в журнал транзакций удаление каждой строки | TRUNCATE TABLE удаляет все строки в таблице, не записывая при этом удаление отдельных строк данных в журнал транзакций |
| Для DELETE требуется больший объем журнала транзакций | Для TRUNCATE TABLE требуется меньший объем журнала транзакций, за счет того, что она не заносит в журнал запись для каждой удаляемой строки |
| DELETE выполняется медленнее | TRUNCATE TABLE выполняется быстрее также за счет меньшего использования журнала транзакций |
| DELETE удаляет строки по одной за раз | TRUNCATE TABLE удаляет все строки в таблице путем освобождения страниц |
| DELETE создает блокировку на каждую строку, которую требуется удалить из таблицы | TRUNCATE TABLE блокирует в целом таблицу и страницу, чтобы удалить все записи |
| DELETE сохраняет счетчик идентификаторов и не сбрасывает его до начального значения | TRUNCATE TABLE сбрасывает счетчик идентификаторов до начального значения |
| DELETE может активировать триггер | TRUNCATE TABLE не может активировать триггер, поскольку в данном случае в журнал не записываются удаление отдельных строк |
| DELETE сохраняет статистику объекта и выделенное пространство | TRUNCATE TABLE освобождает все страницы данных таблицы, поэтому TRUNCATE удаляет всю статистику и выделенное пространство |
| DELETE можно использовать, если таблица является частью индексированного представления | TRUNCATE TABLE нельзя использовать, если таблица является частью индексированного представления |
| Чтобы использовать DELETE, необходимы соответствующие права на удаление данных | Чтобы использовать TRUNCATE TABLE, у Вас должны быть права на ALTER TABLE, т.е. на изменение таблицы |
Таким образом, проанализировав таблицу, Вы можете решить, в каком случае для Вас будет лучше использовать TRUNCATE TABLE, а в каком DELETE.
Самым очевидным здесь является то, что если Вам нужно удалить не все данные из таблицы, а только часть, т.е. по условию WHERE, то Вы можете использовать только DELETE.
А, например, если Вам приходится периодически очищать всю таблицу, и значения идентификаторов не важны, то вполне возможно предпочтительней будет использовать TRUNCATE TABLE, так как это будет быстрей, и ресурсов затрачиваться будет меньше.
Пример отличия в работе TRUNCATE TABLE от DELETE
Для примера давайте создадим таблицу со столбцом идентификаторов, затем добавим в нее данные и удалим их сначала инструкцией DELETE, а потом попробуем выполнить то же самое только инструкцией TRUNCATE TABLE (предварительно пересоздав таблицу).
Создание тестовой таблицы
Выполняем удаление с помощью DELETE
Выполняем удаление инструкцией TRUNCATE TABLE
Как видите, в первом случае счетчик не сбросился, и нумерация продолжилась, а во втором счетчик начался с самого начала.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
TIL: Three differences between TRUNCATE and DELETE
Both DELETE and TRUNCATE remove records from a table. But, these days I learned three differences between TRUNCATE and DELETE statements in SQL Server. Let me share them with you.
DELETE accepts a WHERE condition to only remove some records, TRUNCATE doesn’t. DELETE doesn’t reset identity columns to its initial value, but TRUNCATE does. And, DELETE fire triggers, TRUNCATE doesn’t.
To see these three differences in action, let’s create a sample database with a Movies table. It only contains an auto-incremented id, a movie title and an score.
1. TRUNCATE doesn’t accept a WHERE clause
The first difference is about the WHERE clause.
DELETE accepts a WHERE clause to only delete some records from a table. But, TRUNCATE doesn’t. It deletes all records from a table.
If you try to add a WHERE clause with TRUNCATE, you get “Incorrect syntax near the keyword ‘WHERE’“.
2. TRUNCATE resets Identity columns
An identity column is a column with automatic incremented values. It’s used to create key values in tables.
Values for identity columns start from a “seed” value and increase by an “increment” value. You can use any number as seed and any positive or negative number as increment. By default, if you don’t use any seed or increment, it starts from 1 and increments by 1. IDENTITY = IDENTITY(1, 1)
DELETE statements don’t reset identity columns. It means new rows will have the next value in the identity columns. But, TRUNCATE does reset identity columns. The next new row will have the seed in the identity column.
Let’s delete all movies from our sample table and see the Id columns for the new movies.
DELETE doesn’t reset identity columns
Notice how ‘Avatar’ still has the Id = 4 after deleting all movies.
Now, let’s see how TRUNCATE resets the identity column. This time, let’s use TRUNCATE instead of DELETE.
TRUNCATE resets identity columns
Notice the Id of ‘Platoon’. It’s 1 again. When we created our Movies table, we used the default seed and increment.
3. TRUNCATE doesn’t fire Triggers
A trigger is an special type of store procedure that runs when a given action has happened at the database or table level.
For example, you can run a custom action inside a trigger after INSERT, DELETE or UPDATE to a table.
When you work with triggers, you have two virtual tables: INSERTED and DELETED . These tables hold the values inserted or deleted in the statement that fired the trigger in the first place.
Now, back to the differences between TRUNCATE and DELETE. DELETE fires triggers, TRUNCATE doesn’t.
Let’s create a trigger that shows the deleted values. It uses the DELETED table.
Now, let’s delete our movies with DELETE and TRUNCATE to see what happens. First, let’s add some new movies and let’s use the DELETE.
DELETE fires triggers
Notice the results tab with our three sample movies. Now, let’s use TRUNCATE.
TRUNCATE doesn’t fire triggers
Now, after truncating the table, we only see in the messages tab the number of rows affected. No movies shown.
Bonus: Rollback a TRUNCATE
We can rollback a TRUNCATE operation. To see this, let’s add our three movies and use a ROLLBACK with some SELECT’s in between.
You can rollback a TRUNCATE
Notice, the two results. The one inside the transaction, before the rollback, is empty. And the last one, after the rollback, with our three movies.
SQL DELETE and TRUNCATE Statement
The DELETE Statement is used to delete rows from a table.
Syntax of SQL DELETE Statement :
DELETE FROM table_name [WHERE condition];
The Where clause in above command is used to identify the rows in the column that need to get deleted.
If we don’t include the WHERE clause, all the rows in the table will be deleted.
Example :
Using DELETE Statement we will delete a record of employee from table EmployeeDetails1 whose >
We will delete Ajay’s record from the table, the query for this would be like
delete from EmployeeDetails1 where >
The record of Ajay got deleted.
To delete all rows or records from EmployeeDetails1 table, the query would be like
delete from EmployeeDetails1
All the records from table EmployeeDetails1 got deleted.
SQL TRUNCATE Statement :
The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax of SQL DELETE Statement :
TRUNCATE TABLE table_name;
Example :
We will delete all the records from table EmployeeDetailsADD using SQL TRUNCATE Statement, the query for this would be like
TRUNCATE TABLE EmployeeDetailsADD
Before Truncate Statement:
After Truncate Statement :
All the records from table EmployeeDetailsADD got deleted.
Difference between DELETE and TRUNCATE :
TRUNCATE is a DDL( Data Definition Language) statement while DELETE is a DML(Data Manipulation Language) statement.
We cannot use Where clause in TRUNCATE while in DELETE we can use Where clause.
TRUNCATE removes all rows from a table.
DELETE also removes all rows from a table but is also used to remove rows from a table based on WHERE condition.
TRUNCATE is faster than DELETE because minimum logging takes place in transaction log while in DELETE it maintains the log.
DELETE operations can be rolled back while TRUNCATE operations cannot be rolled back.
Want a quick revision on SQL basics, suggesting to view following one hour length complete video: —
Как очистить таблицу в MySQL
В статье мы расскажем, как в MySQL очистить таблицу. Мы покажем два способа и объясним разницу между ними.
Очистить таблицу можно при помощи одной из команд:
- TRUNCATE,
- DELETE.
Разница между TRUNCATE и DELETE
Команда TRUNCATE является оператором DDL. DDL (Data Definition Language) — это язык определения данных. Операторы языка DDL управляют объектами баз данных: удаляют, создают или переименовывают объекты БД.
Команда DELETE является DML-оператором. DML (Data Manipulation Language) — это язык манипуляции данными. Операторы языка DML позволяют вставить, удалить, изменить, извлечь или обновить данные в базе.
Сравним работу команд:
| TRUNCATE | DELETE |
|---|---|
| Удаляет все данные из таблицы | Может удалить часть данных в соответствии с условием WHERE |
| Удаляет все строки из таблицы освобождением страниц | Удаляет строки по одной |
| Записывает в журнал транзакций сведения о каждой удалённой странице, а не строке | Делает запись в журнал транзакций при удалении каждой строки |
| Работает быстрее | Работает медленнее |
| Нужны привилегии ALTER | Нужны привилегии DELETE |
| Сбрасывает идентификаторы | Не сбрасывает идентификаторы |
| Блокирует таблицу и страницу перед удалением | Блокирует строку перед её удалением |
Выбор команды зависит от конкретного случая. Если нужно удалить некоторые строки по условию, подойдёт только DELETE. Если нужно полностью очистить таблицу и сбросить идентификаторы, используйте TRUNCATE.
Понять разницу между командами и определиться с их выбором поможет таблица с операторами каждого языка:
| DDL | DML |
|---|---|
| CREATE | SELECT |
| ALTER | INSERT |
| DROP | UPDATE |
| TRUNCATE | DELETE |
| COMMENT | MERGE |
| RENAME | CALL |
| EXPLAIN PLAN | |
| LOCK TABLE |
Таким образом, операторы DDL управляют структурой, а операторы DML — её содержимым.
Как в MySQL очистить таблицу
Подключитесь к серверу по SSH. Затем подключитесь к MySQL при помощи команды:
Вместо username введите имя пользователя, вместо password — пароль.
Если вы не знаете пароль, попробуйте войти без него при помощи команды:
Если подключение без пароля не настроено, возникнет ошибка:
В этом случае сбросьте пароль от root-пользователя MySQL по инструкции.
TRUNCATE
TRUNCATE полностью очищает таблицу без возможности указать дополнительные условия. Для этого:
Выберите базу данных, в которой находится таблица, которую вы хотите очистить:
Вместо db_name введите имя базы данных.
Очистите таблицу при помощи команды:
TRUNCATE позволяет указать название БД и название таблицы в одном запросе. Для этого используйте команду:
Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.
Готово, вы очистили таблицу.
DELETE
Выберите базу данных, в которой находится таблица, которую вы хотите очистить:
Вместо db_name введите имя базы данных.
Очистите таблицу при помощи команды:
Вы можете указать название БД и таблицы в одном запросе:
Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.
Готово, вы очистили таблицу при помощи DELETE.
DELETE позволяет использовать условие WHERE, чтобы удалить некоторые строки из таблицы:
DELETE FROM table_name WHERE condition;
Где condition — это условие.
Пример команды, в котором будут удалены все строки, значение столбца, id которых больше 1000:
DELETE FROM table_name WHERE id > 1000;
Как проверить содержимое таблицы
Если вы хотите убедиться, что в таблице не осталось данных, воспользуйтесь командой:
Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.