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

0 votes
30 views
Ethan Karla asked 4 days ago in Database by Ethan Karla

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

1 Answer

0 votes
Ashutosh Kumar Verma answered yesterday by Ashutosh Kumar Verma

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?