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 do I find information about the install locations for the various instances running on a computer?

1125

---

Tell me what are the advantages of using stored procedures?

1062

---

What is the use of tempdb? What values does it hold?

1120

---

How do you clear a log file?

1110

---

Is port 1433 secure?

1039

---

In what version of sql server were synonyms released?

1000

---

What will be the maximum number of indexes per table?

1019

---

What is ems sql management studio? : sql server management studio

1168

---

In which format does an image save in SQL Server database ?

1057

---

Does windows server 2016 come with sql server?

986

---

List some of the rules that apply to creating and using a ‘view’

925

---

What are the different types of stored procedures?

1155

---

What happens if you are trying to access a schema not owned by you?

1020

---

What happens to a trigger with multiple affected rows?

996

---

What is self contained scalar sub query?

1016

---