实例理解SQL中truncate和delete的区别_MsSql_

 ---创建表Table1 IF OBJECT_ID('Table1','U') IS NOT NULL DROP TABLE Table1 GO CREATE TABLE Table1 (ID INT NOT NULL, FOID INT NOT NULL) GO --插入测试数据 INSERT INTO Table1 VALUES(1,101),(2,102),(3,103),(4,104) GO ---创建表Table2 IF OBJECT_ID('Table2','U') IS NOT NULL DROP TABLE Table2 GO CREATE TABLE Table2 ( FOID INT NOT NULL) GO --插入测试数据 INSERT INTO Table2 VALUES(101),(102),(103),(104) GO SELECT * FROM Table1 GO SELECT * FROM Table2 GO 

 CREATE TRIGGER TG_Table1 ON Table1 AFTER DELETE AS BEGIN DELETE FROM TA FROM Table2 TA INNER JOIN deleted TB ON TA.FOID=TB.FOID END GO 

 ---测试DELETE删除操作 DELETE FROM Table1 WHERE ID=1 GO ---执行触发器成功,Table2表中的FOID=101的数据也被删除 SELECT * FROM Table1 GO SELECT * FROM Table2 

 ---测试TRUNCATE删除操作 TRUNCATE TABLE Table1 GO ---Table2中的数据没有被删除 SELECT * FROM Table1 GO SELECT * FROM Table2 

 ---查看TRUNCATE和DELETE的日志记录情况 CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GO DELETE FROM Table2 WHERE FOID=102 GO SELECT * FROM fn_dblog(NULL,NULL) 

 ----TRUNCATE日志记录 CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GO TRUNCATE TABLE Table2 GO SELECT * FROM fn_dblog(NULL,NULL) GO