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 the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

993

---

How to modify existing triggers using "alter trigger"?

953

---

What is sub-query in sql server? Explain its properties.

1049

---

What are distributed partitioned views?

1016

---

what are different types of backups available in sql server? : Sql server database administration

902

---

What is the osql utility?

923

---

What are the advantages of passing name-value pairs as parameters?

1126

---

What is for xml in sql server?

995

---

How do I save a stored procedure in sql server?

947

---

What are the lambda triggers?

865

---

Explain the types of indexes.

1128

---

How to get the definition of a trigger back?

968

---

Can you import Microsoft Excel data to SSRS?

126

---

How to find the source of a table in sql server?

938

---

What is the Disadvantage of indexed sequential file.

1033

---