DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 32--------------------------------------------------
       TRUNCATE  AND DELETE TABLE with LOG ROWS DEMO
       By: srinivasma_exceldbp Date: March 24, 2014, 12:54 am
       ---------------------------------------------------------
       /*
       1. Both Truncate and Delete table 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 delloacation
       */
       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 if a table is truncated it can
       be recovered
       --** within a transaction
       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,*
       from  fn_dblog (null, null)
       --** Check for Description like deallocated
       rollback
       begin tran
       truncate table truncatetest
       select 'DELETE' as Operation, *
       from  fn_dblog (null, null)
       --** Check for Description like deallocated
       rollback
       -- Execute End
       --==============================================================
       ======
       -- Clean Up : Drop demotest database
       use master
       go
       drop database  demotest
       go
       --==============================================================
       ======
       *****************************************************