DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 69--------------------------------------------------
       Comparison between TRUNCATE TABLE and DELETE 
       By: srinivasma_exceldbp Date: May 21, 2014, 5:34 am
       ---------------------------------------------------------
       /*
       1. Both Truncate and Delete can be rolled back
       2. If all rows in a table are deleted , use truncate table
       3. truncate table generates less log rows compared to delete
       4. check description column from fn_dblog for delloacated
       */
       create database demotest
       go
       use demotest
       go
       create table truncatetest ( keyid int identity(1,1),keydesc
       char(8000) )
       go
       set nocount on
       declare @keydesc char(8000),@slno int,@charslno int
       set @slno
       set @keydesc
       while @slno < 52
       begin
       set @slno = @slno + 1
       select @charslno = @slno + 64
       set @keydesc = replicate ( char(@charslno),8000)
       insert into truncatetest (keydesc) values (@keydesc)
       end
       select * from truncatetest
       go
       --==============================================================
       ==========
       --** Below rollback shows , even table is truncated it can be
       recoverd
       --** within a trasaction
       begin tran
       select 'Before truncate',count(keyid) as noofrows from
       truncatetest --52
       truncate table truncatetest
       select 'After truncate',count(keyid)  as noofrows from
       truncatetest --0
       rollback
       select 'After rollback',count(keyid)  as noofrows from
       truncatetest -- 52
       --==============================================================
       ======
       --** Check log rows . Differnce between Truncate and Delete
       --** Truncate will have less log rows compared to Delete
       --** Check truncate command
       -- Execute Start
       checkpoint ;
       begin tran
       select 'Before truncate',count (*) as logreccount
       from  fn_dblog (null, null);
       truncate table truncatetest
       select 'After truncate', count (*) as logreccount
       from  fn_dblog (null, null);
       rollback
       --** Check delete command
       begin tran
       select 'Before delete',count (*) as logreccount
       from  fn_dblog (null, null);
       delete from truncatetest
       select 'After delete',count (*) as logreccount
       from  fn_dblog (null, null);
       rollback
       -- Execute End
       --==============================================================
       ======
       --** Check log rows details between truncate and delete
       checkpoint ;
       -- Execute Start
       begin tran
       truncate table truncatetest
       select 'TRUNCATE' as Operation, Description
       from  fn_dblog (null, null)
       where Description like '%deallocate%'
       --** Check for Description like deallocated
       rollback
       begin tran
       truncate table truncatetest
       select 'DELETE' as Operation, Description
       from  fn_dblog (null, null)
       where Description like '%deallocate%'
       --** Check for Description like deallocated
       rollback
       -- Execute End
       --==============================================================
       ======
       -- Clean Up : Drop demotest database
       use master
       go
       drop database  demotest
       go
       --==============================================================
       ======
       *****************************************************