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

What are policy management terms?

736

---

What is the difference between Clustered and Non-Clustered Index?

775

---

What does COMMIT command do?

767

---

What is executereader?

720

---

How data can be copied from one table to another table?

697

---

---

What are examples of triggers?

808

---

What is repeatable read?

693

---

what do you understand by change data capture?

744

---

What is dbcc? Give few examples.

748

---

What's new in sql management studio for sql server? : sql server management studio

766

---

What is log in sql server?

758

---

What options are there to delete rows on the publisher and not on the subscriber? : sql server replication

883

---

Write a sql query to sort on different column name according to the parameters passed in the function?

693

---

What are character string data types in ms sql server?

792

---

Hi all, can any one please tell me the difference between sql server 2008 and orace 9i

1713

---