C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Offshore Development    
Silverlight Tutorials | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...
New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !






Difference B/W Truncate,Delete and Drop Table


Posted Date: 19 Jun 2008    Resource Type: Articles    Category: Databases
Posted By: Gaurav Agrawal       Member Level: Silver
Rating:     Points: 5



DROP : Drops the table records and structure from the DB schema. Nothing else is leftout. Can't rollback.

Truncate
: Clears the records from the table but the table structure still remains.
Can't roll back. Clears all records from the table because we can not use where clause in truncate command.It is fast because it delete all rows at a time not onr by one.and will make only one entry in log file for deleting all the row, THAT ALSO WE CAN ROLL BACK. it will reset Identity value of the identity column

Delete from
: deletes the rows based on the query or will delete all the rows if no "where" clause is given. Can roll back i.e deleted records can restored. it will not reset identity value of identity column
This operation is slow as compared to drop and truncate command because it makes a entry for every deleted row into the transaction log file and all deleted records can be rolled back after deletion.
in delete it will make one entry for each row to be deleted. while truncate will make only one entry for all the rows. in delete it will free all the datapages by deleting the contents of the data pages. while in truncate it will delete the pointer to those datapages for all deleted rows




Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks      
Popular Tags   What are tags ?   Search Tags  
SQL Server  .  

Post Feedback


This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.
Next Resource: Difference between stored procedure and functions in SQL Server
Previous Resource: SQL Server Compute and Compute By Clause
Return to Discussion Resource Index
Post New Resource
Category: Databases


Post resources and earn money! 		 
Related Resources


dotNet Slackers   BizTalk Adaptors    Web Design

it outsourcing

Contact Us    Privacy Policy    Terms Of Use