Instance to understand the difference between truncate and delete in SQL _mssql

In this paper, we introduce the difference between truncate and delete in SQL by a simple example, and help us understand the following details.

---Create a table Table1
IF object_id (' Table1 ', ' U ') is not a NULL
DROP table Table1 go
CREATE table Table1
(ID int NOT NULL,
foid int ' not NULL ' go

--Inserts test data insert INTO
Table1
VALUES (1,101), (2,102), (3,103), ( 4,104)
Go

---Create a table Table2
IF object_id (' Table2 ', ' U ') is not a NULL
DROP table Table2
Go CREATE TABLE Table2
(
foid INT not NULL) go-
inserts test data insert INTO
Table2 VALUES (101), (102), ( () go to 
select * from Table1 go 
select * Table2
Go

Create triggers in the Table1 table to delete the corresponding foid in the Table2 table when the data in the table is deleted

CREATE TRIGGER tg_table1 on Table1 after
delete
as
BEGIN
 Delete from Ta from Table2 ta INNER JOIN dele Ted TB on TA. Foid=tb. Foid End Go

---Test delete delete operation delete from
Table1 WHERE id=1

Go
---Execute the trigger successfully, the FOID=101 data in the Table2 table is also deleted
SELECT * from Table1
Go
SELECT * from Table2

---Test TRUNCATE delete operation
TRUNCATE TABLE Table1

go
---Table2 data is not deleted
SELECT * from Table1
go SELECT * from Table2

---View logging of truncate and deletes CHECKPOINT go
SELECT * from Fn_dblog (null,null) go
DELETE From Table2
WHERE foid=102
go
SELECT * from Fn_dblog (null,null)

A lop_delete_rows,lcx_heap delete action log record is recorded on line fourth

----TRUNCATE log record
CHECKPOINT
go
SELECT * Fn_dblog (null,null) go
TRUNCATE TABLE Table2
Go
SELECT * from Fn_dblog (null,null) Go

Truncate operation does not record delete log action

The main reason is because the truncate operation does not activate the trigger because the truncate action does not record the log deletion of each row, so when you need to delete a table's data, you need to consider whether it should be done if there is a log delete operation, rather than a personal custom.

This is all in this article, and I want to help you to differentiate the use of truncate and deletes in SQL.