Difference Between TRUNCATE , DELETE and DROP in SQL Server

Difference between TRUNCATE, DELETE and DROP in SQL Server: In this post, we are sharing about one of the important interview questions which are asked in every interview. so we are trying to explain in simple words so that everyone can easily understand and get an idea about:

  • Truncate in SQL
  • Truncate meaning
  • Difference between Delete and truncate
  • Truncate table
  • Difference between a drop and truncate
  • Drop column or table in SQL
  • Truncate command in SQL
  • Difference between delete drop and truncate

Go through the below description, with sample hope u can understand better.

DELETE Command in SQL

  • The DELETE Statement is used to delete rows from a table.
  • Syntax of a SQL DELETE Statement: DELETE FROM table_name [WHERE condition];
  • NOTE: The WHERE clause in the SQL delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table are deleted, so be careful while writing a DELETE query without WHERE clause.
  • SQL DELETE Example: To delete an employee with id 100 from the employee table, the SQL delete query would be like,
    DELETE FROM employee WHERE id = 100;

Truncate Meaning in SQL

  • The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
  • Syntax to TRUNCATE a table: TRUNCATE TABLE table_name;
  • SQL TRUNCATE Statement Example: To delete all the rows from the employee table, the query would be like,
    TRUNCATE TABLE employee;

DROP Table | Column In SQL

  • This SQL command is used to remove an object from the database. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using the DROP command. When a table is dropped all the references to the table will not be valid.
  • Syntax to drop an SQL table structure: DROP TABLE table_name;
  • SQL Statement Example:
    DROP TABLE employee;

If you like SoftwareTestingo and would like to contribute something to this community, then you can also write an article using our Contact us page or mail your article to admin@softwaretestingo.com. So that we can review your article and that also appears on the SoftwareTestingo.com main page and help other Testers.

Please Improve this article, if you find anything incorrect by commenting on the comment box and we are happy to work on the article to maintain the accuracy and improvement.

I love open-source technologies and am very passionate about software development. I like to share my knowledge with others, especially on technology that's why I have given all the examples as simple as possible to understand for beginners. All the code posted on my blog is developed, compiled, and tested in my development environment. If you find any mistakes or bugs, Please drop an email to softwaretestingo.com@gmail.com, or You can join me on Linkedin.

Leave a Comment