Zero To Hero In MS SQL Server

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,

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, databases, database management systems, 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 the Grade is.

A DELETE statement is used to delete a particular data from the table. Like the drop statement, here the entire table is not removed. Only the particular data is removed from the table. DELETE statement is a DML command. We need to use the 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., remove all rows from the table.

Points To Remember

Drop statement will remove the table from the database. We cannot roll back the table.
A delete statement is used to delete a particular data from the table using the WHERE clause. Without the WHERE clause, the user cannot delete 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.