Avoid Mistakes: Understanding Delete and Truncate in SQL

SQL Tutorial Delete and Truncate Difference Interview Question Learn SQL with NK Chapter 8 Joins in SQL : https://youtu.be/UgxcJGu2m7k Chapter 7 Everything about SELECT Statement : https://youtu.be/ilVVa8uVNeM Chapter 6 DDL Statements : https://youtu.be/DocoAjPqHss Chapter 5 Constraints in SQL : https://youtu.be/nv6Ow0emAJ0 Chapter 4 Datatypes in SQL : https://youtu.be/PedrfjN-PCo Chapter 3 Command Types in SQL : https://youtu.be/VZtRsZ4klhc Chapter 2 Oracle Live Free :- https://youtu.be/g257vfnM2mo Chapter 1 MySQL Download & Installation :- https://youtu.be/ErtPrysapLc In this tutorial we will see the difference between truncate and a delete in in SQL difference between delete and truncate is a interview question if you are appearing for the SQL interviews so let's see first of all let's see the similarity between these two statements delete statement deletes the records from the table whereas truncate statement also deletes the records from the table so the question is what is the difference between them and what is their purpose having delete and truncate separate first of all delete is DML command which is data manipulation language command truncate is a ddl command that is data definition language command difference number two delete statement delete desired number of records from the table you can Target a particular records to be deleted whereas truncate statement deletes all the records from the table at one time you do not have a choice or you you cannot you do not have option to select a particular records while truncating it number three you can use where Clause with a delete to restrict the number of Records whereas if you cannot use where Clause with the truncate statement and that is the reason you cannot Target a particular records in a truncate statement option difference number four delete records with a delete statement can be revoked or restored back so after the delete statement you you you think that you don't want to delete it you want to restore them back so it is possible by using a particular statement called as rollback whereas truncate in a truncate the truncated records with the truncate statement cannot be revoked or restored once truncated it's it's done you cannot you cannot restore them back then the difference number five delete is slow as compared to the truncate and truncate is faster as compared to the delete if you have millions of records and you are deleting something from a particular table in that case delete will be slow whereas if you are truncating a table having millions of Records it will be faster next delete the delete statement deletes the records and does not interfere with the tables identity so a table is which will be stored as it is will be there as it is while we are using the delete statement whereas truncate statement does not delete the table structure but resets the identity of the table does not only delete the table actually what happens it's actually while in the back end it is deleting the table and recreating it and that is the difference main basic difference between delete and truncate and that's why it is resetting the identity of the table next delete activates the trigger on the table and causes them to fire so in a SQL we know there are triggers so before delete after delete these are few examples I am giving you those will be activated if we are using the delete statement and delete statement will cause them to fire whereas truncate does not activate the trigger is applied on the table you cannot run the it cannot run the triggers while executing the truncate statement let's see their syntax and delete from table name wear condition where condition is optional but we can use optional so you use where Clause so that we can Target a particular stand records into the database truncate table table name that's it it's over no option of where close or anything like that so these are the statements and these are the differences between the delete and truncate let's see this with example so here I have a employee table which we are going to use to demonstrate the delete and truncate select start from employee let me execute this query we have these many records here in the employee table so let's start with the deleting the records so I wrote here the statement delete from employee where e-name which is employee name equals to king so I'm just targeting this last record and I want to delete that particular last record so if I just go here and select and run it it's executed successfully now once again let's run select star from employee the king record is no deleted so this is how we can Target a particular records into the delete statement using the SQL Delete and Truncate Difference Interview Question