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

How to download microsoft sql server 2005 express edition?

975

---

What is attribute hierarchy? : sql server analysis services, ssas

961

---

What is the difference between a function and a trigger?

1013

---

Explain the difference between function and stored procedure?

897

---

What is the difference between varchar and varchar(max) datatypes?

1002

---

What is apply operator in sql?

957

---

How do you rename a table in sql server?

939

---

Explain can you implement data mining in ssrs?

145

---

When would you use it?

943

---

Do you know what guidelines should be followed to help minimize deadlocks?

921

---

What are “unrepeatable reads”?

1176

---

You want to use bids to deploy a report to a different server than the one you chose in the report wizard. How can you change the server url?

147

---

What is page-level compression?

902

---

What are the steps you can take to avoid “deadlocks”?

919

---

Define synonym?

1114

---