Can you tell me the difference between DELETE & TRUNCATE
commands?
- 15 Answers
- 22743 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
Do you know what is a trace frag? Where do we use it?
What are the differences between clustered and non-clustered index?
How to get the definition of a view out of the sql server?
Can I run multiple instances of sql server 2000 at the same time on one computer?
What are the steps you should follow to start sql server in single-user mode?
What is lock escalation? : sql server database administration
How to store and query spatial data?
What is t-sql script to take database offline – take database online.
application server is slow what may be the problem
What is self contained multi valued query?
How display code or Text of Stored Procedure using Sql query in Sql Server ?
can an order by clause be used in a creation of a view?
Explain the difference between HTTP and HTTPS in database?
What does asynchronous call backs means?
What is merge join?
