What is the difference between the Truncate and Delete commands for example?
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;
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;