Difference Between drop and truncate in sql

Posted by in Sql Server category on for Beginner level | Views : 6892 red flag




 When you drop the Table:

1)    All data and structure in the table is deleted

2)    Any Pending transaction are commited

3)    All Indexes are dropped

4)    You cannot RollBack the DROP Table statement.

5)DROP TABLE is commited automatically

6)Indexes, tables, and databases can easily be deleted/removed with the   DROP statement



DROP INDEX Syntax for MS SQL Server:

DROP INDEX table_name.index_name


The DROP TABLE Statement:

The DROP TABLE statement is used to delete a table

       DROP TABLE dept;

        Table Dropped   

The DROP DATABASE Statement:

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name





  When you Truncate a Table

1)    Removes all Rows from a Table

2)    Releses the storage space used by that table

3)    You cannot roll back row removal when using TRUNCATE.

4)    If the table is Parent of a refrential integrity constraint,you cannot truncate the TABLE.Disable the constraint before issuing the TRUNCATE statement.



 TRUNCATE TABLE tablename;


         tablename is the name of the table.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Posted by: Vuyiswamb on: 5/12/2009
This might be small, but is Vauable information.

Thanks for sharing

Vuyiswa Maseko

Login to post response

Comment using Facebook(Author doesn't get notification)