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.
Learn With Sundar 
Leave a Reply