Re: question about truncate table
From: joe (pearl_77_at_hotmail.com)
Date: 05/04/04
- Next message: Anith Sen: "Re: Can I create Index in a part of a field"
- Previous message: Anith Sen: "Re: Remove non-numeric characters from a string"
- In reply to: Dinesh T.K: "Re: question about truncate table"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 May 2004 15:44:51 -0400
ic, from book online:
It is almost always faster than a DELETE statement with no conditions
because DELETE logs each row deletion, and TRUNCATE TABLE logs only the
deallocation of whole data pages
"Dinesh T.K" <tkdinesh@nospam.mail.tkdinesh.com> wrote in message
news:%23%23anZPgMEHA.2736@TK2MSFTNGP11.phx.gbl...
> Joe,
>
> 'Truncate table ' is minimally logged and data is recoverable.Execute the
> below code which demonstrates that :
>
> <script starts>
>
> SET NOCOUNT ON
> PRINT 'Create a test database'
> CREATE DATABASE TestDb
> GO
> USE Testdb
> GO
> CREATE TABLE SamTab(sno NUMERIC(4),nme VARCHAR(20))
> PRINT 'Created a test table in that test database'
> INSERT INTO SamTab VALUES(1,'Joe')
> INSERT INTO SamTab VALUES(2,'Jane')
> PRINT 'Inserted two rows'
> PRINT 'Before Truncate'
> SELECT * FROM SamTab
> PRINT 'Start a Transaction'
> BEGIN TRANSACTION
> PRINT 'Truncate Table'
> TRUNCATE TABLE SamTab
> PRINT 'After Truncate'
> SELECT * FROM SamTab
> PRINT 'Roll back transaction'
> ROLLBACK TRAN
> PRINT 'Your Data is back'
> SELECT * FROM SamTab
> GO
> USE MASTER
> GO
> PRINT 'DROP THE TEST DATABASE'
> DROP DATABASE TestDb
>
> <script over>
>
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "joe" <pearl_77@hotmail.com> wrote in message
> news:O$VcQ#fMEHA.3292@TK2MSFTNGP11.phx.gbl...
> > Sorry for asking too many questions today.
> > the question is, if I truncate a table,
> > then those data are not going to transaction log.
> > So, is it still possible to rollback to get all my data back?
> > I know you can do restore database but assume we don't have any database
> > full/differential backup before. I think it's possible but I am not
100%
> > sure
> >
> >
> >
> >
> >
> >
>
>
- Next message: Anith Sen: "Re: Can I create Index in a part of a field"
- Previous message: Anith Sen: "Re: Remove non-numeric characters from a string"
- In reply to: Dinesh T.K: "Re: question about truncate table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
