After deleting all the rows in the table, both will retain the table structure
* Truncate will delete all rows at a stretch where as delete statement will delete individual rows at a time.
* Truncate is comparatively faster than delete statement.
* Truncate is not a logged activity where as delete statement will create a log record in the LDF file.
* Truncate statement will remove the reference where as delete statement will delete the data itself.
* We cannot provide where condition in truncate statement where as its possible in delete statement.
Venkatesan Prabu MCITP,MCAD,MCTS,CCNA. Worked as a ProjectLead(Senior .Net developer,SQL DBA). Now, Managing Director of KAASHIV INFO TECH, Chennai This Blog aims in serving the community in a better way. This blog is read by developers in 159 countries with average of 400 hits per day. Please post your valuable suggestions and hold my hand to serve the community. Lets make a new world with good thoughts and good minds....... This blog serves the SQL server community all over the world.
What do you mean by removing references?
ReplyDeleteNice question, Its like an object. For object existence we need some reference else it will be collected by the systems cleanup routine.. here its server routine.
ReplyDeleteRegards,
Venkatesan Prabu .J