Sunday, April 24, 2016

Difference between delete, truncate and drop statements

Delete:-
·         Delete statement is used to delete rows from the table.
·         Rollback is possible after delete
·         Delete lock a row table and delete them row by row
·         Data manipulation language (dml)
·         Possibility to use where clause filter to delete specific set of data ex: - where
·         Ex:-delete from transaction where id=5
·         Whenever delete command executed automatically a trigger fired in backend
·         Delete will not reset identity columns values of a table
·         Delete from query require more system resources and it will take long time to execute. It will record all rdbms changes in one row at time in transaction log
·         Syntax: -
      Begin transaction
Delete from transactions
           Select * from transactions
           Rollback
           Select * from transactions
    Truncate:-
·         Sql truncate command is used delete all rows from the table.
·         Roll back is not possible after truncate
·         Truncate will reset the identity columns values to its initial defined values
·         When you use truncate statement it will never use or record much log information so the truncate operation will perform very faster
·         Truncate will not delete data if the table is bind with foreign keys or other constrains or other constrains
·         We cannot use where conditions in truncate statement
·         Data definition language (ddl)
·         Trigger will not be used when truncate operation performed
·         It locks entire table
·         Roll back is not possible in advanced mode.
·         Syntax: -
Truncate table <table name>
 Drop:-  drop is used drop entire table object.