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?
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.
-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 Question | Next Question |
Can you explain Full-Text Query in SQL Server? | Tell me the phases a transaction has to undergo? |