SQL Server Performance Forum – Threads Archive

truncate

hi,<br /><br />is truncate performs two things :- 1. drop table and then 2. create it again<br /><br />i m asking this question as i m having a table which is having identity column<br />1. if i execute SQL statement DELETE from tablename, it delete all the rows and if i further start inserting value identity column start with next value (max(identity) from the deleted rows)<br />2. if execute SQL statement truncate table tablename and then further inserting value, the identity column get started with initial value<br /><br /><br />i want to know the exact working of truncate<br />thanx for reading<br />lots of thanx for answering[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />ekta jaiswal

---

See if this helps:http://www.sql-server-performance.com/q&a118.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

thanx Frankkalis
i think i get my answer ekta jaiswal

---

&gt;&gt;is truncate performs two things :- 1. drop table and then 2. create it again<br /><br />No It delete all rows and reset Identity if any [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

Hi,<br />Delete is logged operation and Truncate is not.<br /><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 />

---

It is not true, check Frank’s reference. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

hi,
Yeah Truncate is *Minimally Logged* [xx(]
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

---

Well, frank’s referance has covered all the diffrences between the two so no further clarification needed i guess????

---

As long as that transaction is not committed the TRUNCATE actions can be revoked. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>