The instance understands the difference between truncate and delete in SQL, sqltruncate

The instance understands the difference between truncate and delete in SQL, sqltruncate

This article uses a simple example to introduce the differences between truncate and delete in SQL. The specific content is as follows:

--- Create table Table1IF OBJECT_ID ('table1', 'U') is not nullddrop TABLE Table1GOCREATE TABLE Table1 (id int not null, foid int not null) GO -- INSERT test data insert into Table1VALUES (1,101), (2,102), (3,103), (4,104) GO --- create table Table2IF OBJECT_ID ('table2', 'U ') is not nullddrop TABLE Table2GOCREATE TABLE Table2 (foid int not null) GO -- INSERT test data INTO Table2 VALUES (101), (102), (103), (104) go select * FROM Table1GO SELECT * FROM Table2GO

Create a trigger in Table 1. When the data in the table is deleted, delete the corresponding FOID in table 2.

CREATE TRIGGER TG_Table1 ON Table1AFTER DELETEASBEGIN DELETE FROM TA FROM Table2 TA INNER JOIN deleted TB ON TA.FOID=TB.FOID ENDGO

--- Test the DELETE operation: delete from Table1 where id = 1GO --- the trigger is successfully executed. Data of FOID = 101 in table 2 is also deleted from select * FROM Table1GOSELECT * FROM Table2

 

--- Test TRUNCATE delete operation truncate table Table1GO --- data in Table2 is not deleted SELECT * FROM Table1GOSELECT * FROM Table2

 

--- View TRUNCATE and DELETE log records CHECKPOINTGOSELECT * FROM fn_dblog (NULL, NULL) godelete from Table2WHERE FOID = 102 GOSELECT * FROM fn_dblog (NULL, NULL)

 

There is a lop_delete_rows and lcx_heap deletion operation log record in the fourth row.

---- TRUNCATE log record CHECKPOINTGOSELECT * FROM fn_dblog (NULL, NULL) gotruncate table Table2GOSELECT * FROM fn_dblog (NULL, NULL) GO

 

The TRUNCATE operation does not record the delete log operation.

The main reason is that the TRUNCATE operation does not activate the trigger, because the TRUNCATE operation does not record the log deletion operations of each row, therefore, when you need to delete the data of a table, you need to consider whether to delete the log if there is a record, rather than according to your habits.

The above is all the content in this article. It is helpful for you to differentiate the use of truncate and delete in SQL.

Articles you may be interested in:

• Detailed description of the differences between DROP, TRUNCATE, and DELETE in MySQL. mysql starts from scratch.
• Comparison between Delete and Truncate statements for MySQL DATA Deletion
• Example of two ways for t-SQL to clear table data (truncate and delete)
• Usage of drop, truncate, and delete statements in sqlserver
• Mysql delete operation (delete + TRUNCATE)
• Similarities and differences between drop, delete, and truncate in SQL