Clear all rows : Among Delete, Truncate and Drop

Question

Post reply

Clear all rows : Among Delete, Truncate and Drop

Sharul Nizam

Ten Centuries

Points: 1302
More actions
September 6, 2006 at 6:26 pm

#94363

Hi,
If one table contains 200 million rows, what's the faster SQL scripts can clear all rows in that table? Among Delete, Truncate and Drop, which is the best for performance?
Delete?
Truncate?
Drop and Create new table?
regards.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Haryadi Santoso Old Hand Points: 334 More actions · Answer 1

Hi Nizam,

I think it depends on what you need exactly,

if u need to clear all rows in the table using "delete" syntax,

but if u need to delete the table also using "drop" syntax...

I don't think the difference between those syntax are about how fast the execution time, but only in the functionality.

Haryadi Santoso Old Hand Points: 334 More actions · Answer 2

Hi Nizam,

I think it depends on what you need exactly,

if u need to clear all rows in the table using "delete" syntax,

but if u need to delete the table also using "drop" syntax...

I don't think the difference between those syntax are about how fast the execution time, but only in the functionality.

Haryadi Santoso Old Hand Points: 334 More actions · Answer 3

Hi Nizam,

I think it depends on what you need exactly,

if u need to clear all rows in the table using "delete" syntax,

but if u need to delete the table also using "drop" syntax...

I don't think the difference between those syntax are about how fast the execution time, but only in the functionality.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Unless you want the log to remember that you deleted 200 million rows, DELETE is NOT the way to go...

TRUNCATE is absolutely the fastest way to empty a table, without logging and without disturbing the schema.

If you have foreign keys and you're deleting from the parent table, neither truncate nor delete will work until you clear the children. The exception to that rule is that you can delete from a parent even if children are present if the foreign keys are of the CASCADE ON DELETE nature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

Delete on table with lot of rows will take a lot of time to commit all those Xaction.

If you want to wipe away then use Truncate table

Thanks

Sreejith

Bala Sivaraman SSC Enthusiast Points: 102 More actions · Answer 6

Also depends if the table has an Identity column. If you need the identity column reset, i think you should be using TRUNCATE.

Correct me if am wrong!!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

You are not wrong. Conversely, if you don't want the IDENTITY column to be reset to the SEED value, you must use DELETE, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)