Can you tell me the difference between DELETE & TRUNCATE
commands?
- 15 Answers
- 20446 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 you can get the list of largest tables in a database?
Which are ddl commands?
What is the purpose of the tempdb database?
What do you understand by user-defined function in the sql server?
Explain the microsoft sql server delete command? : SQL Server Architecture
Why is normalisation important?
What are the five characteristics of good data?
Explain few of the new features of sql server 2008 management studio
What is the status of services on passive node for failover cluster in sql server? : sql server database administration
How will you monitor replication latency in transactional replication? : sql server replication
How many types of database relationship in sql server?
What is sql view?
1.what are diff types of joins , and explain diff between cross join and full outer join 2.diff types of views 3. Diff types of index 4. What is diff b/w stores procedure and function procedure 5.diff between double and int in SQL 6.diff between char and varchar in SQL. 7.Oracle or SQL whice you will preferred and why.
What is the use of for clause?
What is 2nf normalization?
