Can you tell me the difference between DELETE & TRUNCATE
commands?
- 15 Answers
- 22526 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
How can I track the changes or identify the latest insert-update-delete from a table?
How to count duplicated values in a column in ms sql server?
What is difference between views and stored procedures?
Explain error handling in ssis?
What is the parse query button used for?
How to use “drop” keyword in sql server and give an example?
Which table keeps information about stored procedures?
How to delete existing rows in a table?
Do you know what are various aggregate functions that are available?
What are the advantages of sql azure?
What do you mean by cardinality?
Explain what is row_number function?
What is exporting and importing utility?
Is t sql the same as sql server?
What is sql server database?
