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