what is the difference between Delete and Truncate??

[BabuLives]

what is the difference between Delete and Truncate in sql??

Post ur answers here..

Regards,
Satheesh

[Selvakumar]

Delete and Truncate are functionally same...

--->>Delete only deletes the specified rows in a Table....

--->>Truncate not only deletes the rows but also reclaims the memory
occupied by them!!!

Thats the major difference...

Regards,
Selva..

[Thread]

Delete can be rolled back where as trucate cannot

[brainstormingguys]

Hai,

The major difference is the TRUNCATE never uses the roll back segments but the DELETE statement uses the rollback segments.That's why we are able to rollback the deleted records when we use the DELETE command and we give the ROLLBACK statement.

Next one is we can't use the WHERE clause in TRUNCATE but we can use the WHERE clause in DELETE.

Hope it helps you a bit.

 

Regards,

Venkatarajan A

--
Plan for the Worst,
Hope for the Best

[BabuLives]

hai...
i like to add some more points to the discussions..

The user who can DELETE rows does not mean they can TRUNCATE the table.
By default, they need at least DDL_ADMIN to do that and delete triggers
are not fired when using TRUNCATE.

Regards,
Satheesh

[Dhaw]

If your table have identity column.And that table contains 5 rows (identity column value = 1...5)

 

now if you use DELETE statement to remove all rows and then try to INSERT a row , the identity column value = 6

but if you use DELETE statement to remove all rows and then try to INSERT a row , the identity column value = 1
 

As selva said

 

"Delete only deletes the specified rows in a Table....

Truncate not only deletes the rows but also reclaims the memory occupied by them"

 

Thanks

Skymania

[Dhaw]

Sorry i have mentioned both as "DELETE"...correction follows

 

If your table have identity column.And that table contains 5 rows (identity column value = 1...5)

 

now if you use DELETE statement to remove all rows and then try to INSERT a row , the identity column value = 6

but if you use TRUNCATE statement to remove all rows and then try to INSERT a row , the identity column value = 1
 

[Siva CH]

DELETE is a logged operation. Every Delete transcation is logged. This can be some time costly if there is  a big chunk of data being deleted for purpose in your application.

 

Where as Truncate is a non logged operation and is faster. Truncate statement can not be applied on a table with a Foreign key constraint. Truncate statement can be rolled back.

 

Regards

Siva

 

On 9/30/05, BabuLives <sathees...@gmail.com> wrote:

[Bala murugan]

Hi,

   I have one doubt

    Trucate statement can be rolled back or delete statement can be rolled back because in this e-mail doc  : siva ch  told truncate can be rolled back but  manish told delete can be rolled back
 .....

Regards,
bala

[Selvakumar]

Hii Bala,
Truncate cannot be rolled back...Where as Delete can be
rolled back..So dont get confused...

Regards,
Selva..

[Shivprasad Koirala]

Yep truncate can not be rolled back as truncate do not maintain any
transaction logs. But that adds + point to truncate they are faster
than delete.
-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/