SQL Database Concepts Question:

Download Job Interview Questions and Answers PDF

Do you know what is Similarity and Difference between Truncate and Delete in SQL?

Basic SQL Server Interview Question
Basic SQL Server Interview Question

Answer:

Similarity

-These both command will only delete data of the specified table, they cannot remove the whole table data structure.

Difference

-TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

-We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.

-TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.

-We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE.

-If table is referenced by any foreign key constraints then TRUNCATE will not work.

Download Basic SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
Can you explain Full-Text Query in SQL Server?Tell me the phases a transaction has to undergo?