What is the difference between the Truncate and Delete commands with example?

Asked 14-Sep-2021
Viewed 482 times

0

What is the difference between the Truncate and Delete commands for example?


1 Answer


0

SQL Truncate and Delete command :

    Both SQL truncate and Delete commands are used for delete all records from a table.

Difference between SQL Truncate and SQL Delete Command :   

                                                                                 Truncate Command                                                                                     Delete Command 
1- The Truncate command is a DDL(Data Definition Language) command.  Delete command is a DML(Data Manipulation Language) command.
2- Truncate command is used to delete all the data from table.   Delete command can delete a specific row of table and also delete all data form.
 3- Where clause is not used in Truncate command     Where clause is used in Delete command.
4- Truncate command is delete all data from table at a time. Delete command can delete a row where the WHERE clause fulfill the condition.
5- Data delete by using Truncate command cannot be rollback  Data is deleted by using Delete command can rollback.

Example :     All the data are deleted from 'StudentDetails' Table by using below statement

                       truncate table StudentDetails;

                   What is the difference between the Truncate and Delete commands with example?

Example :  1- To delete a specific record or row of table you can use following statement

 delete from StudentDetails where studentid=100;

Note - you can select any field in table which is uniquely defined a row.

2- To delete all records from StudentDetails table by using delete command,

delete from StudentDetails;

                         What is the difference between the Truncate and Delete commands with example?