truncate and delete | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

truncate and delete

hi,<br />what ia basic difference between truncate and delete<br />please resolve my query with suitable example , if possible??<br /><br />thanx<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />ekta jaiswal
Refer this Madhivanan Failing to plan is Planning to fail
There are many diffrences.
Truncate is best performance wise but you need rights to execute it.
Truncate deletes all records but in delete statement you can use where.
Best would be to read article on SSP about this and even refer BOL.
you can even refer to this link for basic interview questions where this kind of quest is answered. and this one to see the use of truncate for resetting identity value.
Hi,<br /><br />BOL Says<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><b><i><br />Deleting All Rows Using TRUNCATE TABLE<br />The TRUNCATE TABLE statement is a fast, <b>nonlogged method of deleting all rows in a table</b>. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and <b>TRUNCATE TABLE logs only the deallocation of whole data pages</b>. TRUNCATE TABLE immediately frees all the space occupied by that table’s data and indexes. The distribution pages for all indexes are also freed. <br /><br />As with DELETE, the definition of a table emptied using TRUNCATE TABLE remains in the database, along with its indexes and other associated objects. The DROP TABLE statement must be used to drop the definition of the table.<br /><br /></i></b><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />HTH<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />