Can you tell me the difference between DELETE & TRUNCATE
commands?
Answers were Sorted based on User's Feedback
Answer / guest
Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.
Is This Answer Correct ? | 39 Yes | 6 No |
Answer / sachin patil
Truncate is DDL Command while Delete is DML Command
Is This Answer Correct ? | 26 Yes | 2 No |
Answer / kmbkrishnan
Delete Command require Log file updation for each row of
deleting process. But the Truncate command not.
So, the Truncate Command is so faster than Delete Command.
Is This Answer Correct ? | 28 Yes | 5 No |
Answer / gk singh
truncate provide faster processing than delete because of
truncate does'n require transaction log uddation
Is This Answer Correct ? | 22 Yes | 2 No |
Answer / vijay dev arya
Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / rahul goel
There are many differences. Some of these are as follows:
1. Delete can be rollback but Turncate can not be rollback.
2. Delete is a DML command and Turncate is a DDL command.
3. Delete command is used for deleting one or more row from
the table whereas Turncate command is used for deleting all
rows from the table.
4. Turncate is fact as compared to delete command.
5. we can use where clause with Delete but where is not
possible with Turncate.
Is This Answer Correct ? | 8 Yes | 2 No |
Answer / kumar
Truncate will fail if the concerned table has foreign key
constraints while delete doesnt.
Is This Answer Correct ? | 15 Yes | 10 No |
Answer / mohsin mohammed abdul
Delete command deletes the data from the tables .. but can
get back on issue of roll back whereas when you issue
truncate deletes the records and oracle implicitly issues
commit command and even when you issue rollback you won't
get the data back
Is This Answer Correct ? | 10 Yes | 6 No |
Answer / afzal_aziz
Truncate:
- deallocates the data pages in a table and only this
deallocation is stored in transaction log
- aquires only table and page locks for the whole table.
since no row locks are used less memory is required (lock
is a pure memory object)
- resets identity column if there is one
- removes ALL pages. NO empty pages are left behind in a
table
- fast(er)
- doesn't fire delete triggers
Delete:
- removes one row at the time and every deleted row is
stored in the transaction log
- aquires table and/or page and row locks for the whole
table
- leaves identity column alone
- can leave empty pages in a table since empty page
removal requires a table lock which doesn't necessarily
happen
- slow(er)
- fires delete triggers
http://weblogs.sqlteam.com/mladenp/archive/2007/10/03/SQL-
Server-Why-is-TRUNCATE-TABLE-a-DDL-and-not.aspx
Is This Answer Correct ? | 8 Yes | 6 No |
Answer / anil
Delete Commands:
it is dml statement
you can specify delete command with critery (where
condition)
syntax: Delete * from tablename where condition
Truncate Command:
it is DDL statement
you can not specify critery with truncate table
syntax: truncate table tablename
Is This Answer Correct ? | 2 Yes | 1 No |
What gets stored inside msdb database?
what is curser.
Explain how does the report manager work in ssrs?
you are provided with the single table having say 4 col ie fname lname age city , now the all records with displying of only fname and lname is required but in this format say my name is abhay khanna it will come like this abhay-khanna rahul-roy gaurav-singh the above format is required
How to get max 100 values from sql server
What are the types of subquery?
Which are ddl commands?
What is difference between index and primary key?
What is the new security features added in sql server 2016? : sql server security
Explain the architecture of ms sql reporting service?
How to receive output values from stored procedures?
what is the Ticketing tool used in Wipro technologies at Bangalore...???