The difference between the delete statement in T-SQL

Document directory

• Differences between Delete statements in T-SQL

Differences between delete statements in T-SQL [label: SQL, statement] delete from and truncate table
What are their similarities?
What are the differences ??
Where can I use it ?? ======================================

1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used.
2. TRUNCATE is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE.
3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation.
4. TRUNCATE cannot trigger any DELETE trigger.
5. You cannot grant anyone the permission to clear tables of others.
6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not.
7. The parent table cannot be cleared.

Note: The delete statement is a delete statement without the where clause.
Similarities
Truncate, delete without where clause, and drop both delete table data.

Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.
2. The delete statement is dml, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.
Truncate, drop is ddl, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.
3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
The truncate statement is released to the minextents extent by default, unless reuse storage is used; The truncate will reset the high water line (back to the beginning ).
4. Speed, in general: drop>; truncate>; delete
5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.
To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.
To delete a table, use drop
Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.
If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

Answer supplement

1. Fast TRUNCATE operation.
TRUNCATE cannot be rolled back after submission (the ROLLBACK command DELETE will be revoked, while the TRUNCATE will not be undone ).
1. You cannot use the ROLLBACK command for TRUNCATE.
3. TRUNCATE operations (indexes) on tables are much faster.
4. TRUNCATE cannot trigger.
5. After the table is cleared, the index values of the table and table are reset to the initial size, while the delete value is not.
6. The truncate table cannot be used for tables referenced by the foreign key constraint (that is, the TABLE where the primary KEY is located, not the TABLE where the foreign key is located.


Generally, all are delete.
Where is it used? Is to delete the data in the database an SQL statement

Answer supplement

I made up for you.
Delete is a delete statement in an SQL statement. It is often used by programmers who are not very efficient.
It can be rolled back in the Process of use, that is, after you delete it, you can return the data, that is, the data is returned and has not been deleted.
SQL triggers can be triggered when a record is deleted, that is, when delete is executed.
If an associated field exists, it will prompt that it cannot be deleted. If there is no associated data, it can be said that Table a (type) and Table B (product) yes. When Table a is deleted, if Table B has a deleted Association record, it will not be deleted. This should be enough for you to use.