| Author |
truncate vs drop and create
|
|
| ranjitjain via SQLMonster.com 2006-03-09, 3:23 am |
| In my DW i have few staging tables.
Every day i truncate the whole table and loads data into it.
Another option is to drop and create the table everyday.
1>What performance gain/impact my sql server will face?----priority question
2>What impact my tx-log need to face?
Any help would be appreciated..
Thanks
--
Message posted via http://www.webservertalk.com
| |
| jackal_on_work@yahoo.com 2006-03-09, 3:23 am |
|
1. Nothing. These all are DDL commands. I dont see any comparative
performance gain.
2. Trucate operation is logged minimally in the transaction log as only
extent deallocations are recorded instead of the records that are being
deleted.
Had the question been asked on 'Delete vs Truncate', it would have been
a 'priority question'.
Regards
Jackal
SQL Server & Oracle DBA
| |
| Alex Cieszinski 2006-03-09, 7:23 am |
| If you're having database with full recovery model being set, and you're
performing "truncate table" on it, then you have to do full database backup
to restart your log backups sequence. All log backups made after "truncate
table" operation until full DB backup was made are useless
"ranjitjain via webservertalk.com" <u16198@uwe> wrote in message
news:5cfc5345fee8b@u
we...
> In my DW i have few staging tables.
> Every day i truncate the whole table and loads data into it.
> Another option is to drop and create the table everyday.
> 1>What performance gain/impact my sql server will face?----priority
> question
> 2>What impact my tx-log need to face?
>
> Any help would be appreciated..
>
> Thanks
>
> --
> Message posted via http://www.webservertalk.com
| |
| Dan Guzman 2006-03-09, 7:23 am |
| TRUNCATE TABLE does not break the log backup chain. Perhaps you are
confusing this DDL statement with the BACKUP LOG...WITH TRUNCATE_ONLY, which
is an entirely different beast. Little logging occurs with TRUNCATE TABLE
regardless of the recovery model.
There are no minimally-logged operations in the FULL recovery model. In
older versions of SQL Server (SQL 7 and earlier), you could perform a
minimally logged operation if the 'select into/bulk copy' database option
was turned on and a minimally logged operation would break the LSN chain.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex Cieszinski" <nomail@thanks> wrote in message
news:O0iqjH2QGHA.4344@TK2MSFTNGP12.phx.gbl...
> If you're having database with full recovery model being set, and you're
> performing "truncate table" on it, then you have to do full database
> backup to restart your log backups sequence. All log backups made after
> "truncate table" operation until full DB backup was made are useless
>
> "ranjitjain via webservertalk.com" <u16198@uwe> wrote in message
> news:5cfc5345fee8b@u
we...
>
>
| |
| Alex Cieszinski 2006-03-09, 7:23 am |
| Thanks for info - it seems that I was misinformed by some (can't remember
which one) SQL Server related book,
and I never checked it by myself.
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%232wtfG3QGHA.5728@tk2msftngp13.phx.gbl...
> TRUNCATE TABLE does not break the log backup chain. Perhaps you are
> confusing this DDL statement with the BACKUP LOG...WITH TRUNCATE_ONLY,
> which is an entirely different beast. Little logging occurs with TRUNCATE
> TABLE regardless of the recovery model.
>
> There are no minimally-logged operations in the FULL recovery model. In
> older versions of SQL Server (SQL 7 and earlier), you could perform a
> minimally logged operation if the 'select into/bulk copy' database option
> was turned on and a minimally logged operation would break the LSN chain.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Alex Cieszinski" <nomail@thanks> wrote in message
> news:O0iqjH2QGHA.4344@TK2MSFTNGP12.phx.gbl...
>
>
|
|
|
|