Какая инструкция удаляет все строки таблицы более быстро чем инструкция delete

В этом уроке вы узнаете, как быстро удалить все строки из таблицы MySQL с помощью SQL запроса.

Удаление данных таблицы

TRUNCATE TABLE удаляет все строки из таблицы быстрее, чем DELETE. Логически, TRUNCATE TABLE похож на оператор DELETE без условия WHERE.

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

Синтаксис

Основной синтаксис TRUNCATE TABLE может быть задан с помощью:

TRUNCATE TABLE table_name;

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

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Vasya Pupkin | 2001-05-01 |   5000 |       4 |
|      2 | Vanya Pupkin | 2002-07-15 |   6000 |       2 |
+--------+--------------+------------+--------+---------+

блок 1

Следующая команда удаляет все строки из таблицы employees:

TRUNCATE TABLE employees;

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

TRUNCATE TABLE в сравнении с DELETE

Хотя DELETE и TRUNCATE TABLE, имеют одинаковый эффект — они работают по-разному. Вот некоторые основные различия между этими двумя утверждениями:

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

блок 3

Description

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

Notes

У вас должна быть привилегия TRUNCATE для таблицы, чтобы ее обрезать.

TRUNCATE приобретает ACCESS EXCLUSIVE замок на каждом столе она работает на, который блокирует все другие параллельные операции на столе. Когда задана RESTART IDENTITY , любые последовательности, которые должны быть перезапущены, также блокируются исключительно. Если требуется одновременный доступ к таблице, вместо этого следует использовать команду DELETE .

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

TRUNCATE не будет ON DELETE триггеры ON DELETE, которые могут существовать для таблиц. Но он будет срабатывать триггеры ON TRUNCATE . Если триггеры ON TRUNCATE определены для любой из таблиц, то все триггеры BEFORE TRUNCATE срабатывают до того, как произойдет какое-либо усечение, и все триггеры AFTER TRUNCATE срабатывают после выполнения последнего усечения и сброса любых последовательностей. Триггеры будут срабатывать в том порядке, в котором должны обрабатываться таблицы (сначала те, которые указаны в команде, а затем все, которые были добавлены из-за каскадирования).

TRUNCATE не является безопасным для MVCC. После усечения таблица будет казаться пустой для параллельных транзакций, если они используют моментальный снимок, сделанный до того, как произошло усечение. См. Раздел 13.6 для более подробной информации.

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

Если задана RESTART IDENTITY , подразумеваемые операции ALTER SEQUENCE RESTART также выполняются транзакционно; то есть они будут отменены, если окружающая транзакция не зафиксируется. Имейте в виду, что если какие-либо дополнительные операции над перезапущенными последовательностями выполняются до отката транзакции, последствия этих операций для последовательностей будут currval() , но не их влияние на currval () ; то есть после транзакции currval() продолжит отражать последнее значение последовательности, полученное внутри неудачной транзакции, даже если сама последовательность может больше не соответствовать этому. Это похоже на обычное поведение currval() после неудачной транзакции.

TRUNCATE может использоваться для сторонних таблиц, если поддерживается оболочкой сторонних данных, например, см. Postgres_fdw .

Examples

bigtable таблицы bigtable и fattable :

TRUNCATE bigtable, fattable;

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

TRUNCATE bigtable, fattable RESTART IDENTITY;

othertable таблицу othertable и выполните каскадирование к любым таблицам, которые ссылаются на othertable через ограничения внешнего ключа:

TRUNCATE othertable CASCADE;

We get the requirement to remove the data from the relational SQL table. We can use both SQL Delete and SQL Truncate statement to delete the data. Understanding differences between these commands helps SQL developers to handle their data well. Additionally, this is a very common question asked in SQL beginner’s interviews.

Tell me the difference between delete and truncate command.

I have seen most of the candidates not familiar with the difference between these commands. This article gives you a complete overview of Delete and Truncate statements along with differences.

SQL Delete Command

We use SQL Delete command in SQL Server to remove records from a table. We can remove all records or use a Where clause to remove records matching the criteria.

Example 1: To remove all records from a table

Suppose we want to remove all records from an Employee table, execute the following query.

Example 2: To remove specific records from a table

Suppose we want to remove all employee records belongs to a particular city San Antonio. We can specify this condition in a Where clause with SQL Delete statement as shown below.

We need to use the string value within the single quotes. We can directly specify value without single quotes in the Where clause such as EmpID in the following query.

In the Actual Execution Plan of a Delete clause, we can see it is using the Clustered Index Delete operator to remove a specific row.

Example 3: SQL Delete statement and identity values

Delete statement removes the records one by one and it logs each entry into the transaction log. It is a DML statement.

Suppose we remove a row from a table using the DELETE statement and that table contains the SQL IDENTITY values. IDENTITY values get generate on every new record insert. We might have a question-

What happens to an identity value once we remove a record?

Let’s look at this scenario using an example. We have the following data in the Employee table.

EmpID column is an identity column. We can check the identity column in a table using sp_help command.

We need to delete the EmpID 1014 from the Employee table.

Once we remove the record, view the record from the table. In the following screenshot, we can see that identity value 1014 is not available because we removed that record from the table.

SQL Delete statement does not reset the identity values in a table. We can use DBCC CHECKIDENT to check current identity value and manually set a new identity value for the identity column.

For example, in the following query, we want to reset the identity values to 500.

If we insert a new record, it uses an identity value 501.

Example 4: Rollback a SQL delete statement transaction

We can roll back a transaction using the delete statement. Let’s use a delete statement with BEGIN Transaction.

We removed the record having EmpId 1010 in the employee table.

We can use to roll back the transaction. Execute this rollback command and view the records in the table. We can see the deleted record gets a rollback and we can see it in the Employee table.

SQL Truncate Command

SQL Truncate is a data definition language (DDL) command. It removes all rows in a table. SQL Server stores data of a table in the pages. The truncate command deletes rows by deallocating the pages. It makes an entry for the de-allocation of pages in the transaction log. It does not log each row deletion in the transaction log.

We cannot specify any condition in the SQL Truncate command. At a high level, you can consider truncate command similar to a Delete command without a Where clause. It locks the table and the pages instead of a row.

SQL Server does not show the actual execution plan of a SQL Truncate command. In the following screenshot, you can see the estimated execution plan.

Example 5: Remove all rows of Employee table using the truncate statement

The SQL truncate command is straightforward; you just need to pass the table name to remove all rows.

Example 6: SQL Truncate command and identity values

In the previous example 3, we explored delete command with the identity values. Delete does not reset identity values. Let’s see how the truncate command behaves with the identity values.

First, execute the command in example 5 to delete all rows of a table.

The table is empty now. Let’s insert a new record in the table.

You can see that Identity value again starts from 1 as defined in the table properties.

Example 7: SQL Truncate command with Rollback

In example 4, w explored to roll back a transaction with the delete command. It is a misconception among DBA’s that we cannot roll back a transaction executed with the TRUNCATE command. Is it true?

Let’s explore this again with an example.

Start a transaction to truncate the employee table.

Once the command is completed, verify that there are no records in the table.

Now, issue the Rollback Tran command and verify the records in the table. We get our data back in the table.

It shows that we can roll back delete as well as truncated command started within a transaction.

Let’s explore the difference between the SQL Delete and SQL Truncate command in the following table.

SQL Delete	SQL Truncate
Delete command is useful to delete all or specific rows from a table specified using a Where clause	The truncate command removes all rows of a table. We cannot use a Where clause in this.
It is a DML command	It is a DDL command.
SQL Delete command places lock on each row requires to delete from a table.	SQL Truncate command places a table and page lock to remove all records.
Delete command logs entry for each deleted row in the transaction log.	The truncate command does not log entries for each deleted row in the transaction log.
Delete command is slower than the Truncate command.	It is faster than the delete command.
It removes rows one at a time.	It removes all rows in a table by deallocating the pages that are used to store the table data
It retains the identity and does not reset it to the seed value.	Truncate command reset the identity to its seed value.
It requires more transaction log space than the truncate command.	It requires less transaction log space than the truncate command.
You require delete permission on a table to use this	You require Alter table permissions to truncate a table.
You can use the Delete statement with the indexed views.	You cannot use the truncate command with the indexed views.
Delete command retains the object statistics and allocated space.	Truncate deallocates all data pages of a table. Therefore, it removes all statistics and allocated space as well.
Delete command can activate a trigger as well. Delete works on individual rows and delete the data. Therefore, it activates a trigger.	The truncate command cannot activate a trigger. The trigger is activated if any row modification takes place. In this command, SQL Server deallocates all pages, so it does not activate a trigger.
Delete command removes the rows matched with the where clause. It also does not remove the columns, indexes, constraints, schema	The truncate command only removes all rows of a table. It does not remove the columns, indexes, constraints, and schema.

Conclusion

In this article, we explored both the SQL Delete and SQL Truncate command to remove the rows in a table. I hope this article helps to answer all your questions related to delete and truncate commands in SQL Server and also understand the difference in these commands. You need to be careful while using the truncate command because it removes all rows in a table.