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 is self join in sql server joins?

1055

---

Explain features and concepts of analysis services?

972

---

What are two difference between sql azure and azure tables?

159

---

What is the largest component inside a field?

1122

---

What are the new scripting capabilities of ssms? : sql server management studio

1063

---

Define tool Manage Statistics in SQL Server 2000 query ?

1088

---

What command do we use to rename a database?

1074

---

How to identify current user in ssrs report?

401

---

How to use old values to define new values in update statements in ms sql server?

1261

---

How to list all login names on the ms sql server?

1138

---

Explain trigger classes i.e. Instead of and after trigger?

875

---

What are the different types of replication you can set up in sql server?

1049

---

Does the order of columns in update statements matter?

911

---

Does sql server 2000 clustering support load balancing?

968

---

How to check table values in sql server?

952

---