Difference Between Drop Table, Truncate Table & Delete Table in MS SQL Server – Zero to Hero Query Master – Part 6

Introduction

In this article, we will discuss the difference between Drop Table, Truncate Table, Delete Table.

To perform dropping, deleting and truncating a table, I am creating three tables. With these three tables let’s look into more: 

  • Students.StudentDetails
  • Students.StudentMarks 
  • Students.StudentClass

Drop Table

The syntax for dropping a table in MS SQL is, 

Syntax

DROP TABLE TableName  

Example

DROP TABLE Students.StudentClass  
  • When the above statement is executed, it will remove the table from the database. When a table is dropped from the database, the user cannot roll back the table.
  • DROP statement is a DDL command.
  • When again the user tries to select values from this dropped table, the system will show an error message as “Invalid object name ‘Students.StudentClass’.” 

Delete Table

The Syntax for deleting a table is, 

Syntax

DELETE FROM TableName WHERE CONDITION...  

Example 

DELETE FROM Students.StudentMarks WHERE Grade='F'  
  • When the above statement is executed, it will delete data from the table where Grade is ”F”.
  • DELETE statement is used to delete a particular data from the table. Like drop statement, here the entire table is not removed. Only the particular data is removed from the table.
  • DELETE statement is DML command. 
  • We need to use WHERE clause to delete from the table. 

Truncate Table

The syntax for Truncating a table is, 

Syntax

TRUNCATE TABLE TableName  

Example

TRUNCATE TABLE Students.StudentDetails 
  • When the above statement is executed, it will remove all the data in the table StudentDetails
  • TRUNCATE is a DDL command.
  • When the truncate statement is used for a table, the entire data in the table will be deleted, i.e, removes all rows from the table.

Points To Remember

  • Drop statement will remove the table from database. We cannot rollback the table. 
  • Delete statement is used to delete a particular data from table using WHERE clause. Without WHERE clause, the user cannot delete a data from the table. 
  • The Truncate statement is used to delete all the records from the table.

Conclusion

In this article, we have learned about dropping a table, deleting a table and truncating a table. I hope this was useful and in my next article, we will learn more details. Please share your feedback in the comment section.

Other Articles in this Series

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: