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.