数据库技术:实例理解SQL中truncate和delete的区别

  ---创建表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