Zero To Hero In MS SQL Server - Part Four

Introduction

This is part four of "Zero To Hero In MS SQL Server", and in this article, you will learn about dropping, deleting and truncating tables in MS SQL Server. To read the previous articles under this series, please follow the below-mentioned links, 

• Zero To Hero In MS SQL Server - Part One
• Zero To Hero In MS SQL Server - Part Two 
• Zero To Hero In MS SQL Server - Part Three

The following are the topics of this article,

• A glimpse at the previous articles
• Drop Table 
• Delete Table 
• Truncate Table
• Points to Remember
• Conclusion

A Glimpse at the previous articles 

In Part One:

• We learned the basics of data, database, database management system, and types of DBMS and SQL. 

In Part Two:

• We learned to create a database and maintain it using SQL statements.
• Best practice methods were also mentioned. 

In Part Three:

• We learned to create a schema, delete a schema, and use the created schema using SQL statements.
• We also learned about the types of tables, table creation, selecting data from a table and alerting the table
• The best practice is to use Schema before the table name while creating. 
• Always specify the table with its schema name

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 TABLE 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.