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 connect Azure federated root database and apply federation in entity framework?

107

---

Explain the difference between function and stored procedure?

942

---

Can we insert data into a view?

1135

---

Explain what are the restrictions while creating batches in sql server?

1068

---

How does SSIS(Sql Server Integration Services) deffer from DTS(Data Transformation Services)?

1208

---

Difference between primary key and clustered index?

1005

---

How to set a database state to offline in ms sql server?

1046

---

How to enter unicode character string literals in ms sql server?

1072

---

How you would rewrite the sql query to return the customerid sorted numerically?

995

---

Can select statements be used on views in ms sql server?

1065

---

what are the different types of SSRS reports?

153

---

What is the function of inner join?

1072

---

How can you find out which stored procedures are recompiling?

918

---

What samples and sample databases are provided by microsoft?

1037

---

What is dynamic cursor in SQL SERVER?

1079

---