Mike Diehl's WebLog - All Comments

progremmer | Andreperdana's Blog

progremmer | Andreperdana's Blog — Wed, 25 Aug 2010 06:22:54 GMT

Pingback from progremmer | Andreperdana's Blog

re: Imaginet is hiring

Paresh Prajapati — Thu, 03 Jun 2010 13:12:23 GMT

Hi,

I am interested for Database Admin

re: Imaginet is hiring

Wesley — Wed, 02 Jun 2010 07:04:50 GMT

And the URL to the company site is?

re: SQL Table stored as a Heap - the dangers within

jonmcrawford — Mon, 03 May 2010 13:16:09 GMT

Darren, see Paul Randall's blog, myth 19/30 from the month of April, www.sqlskills.com/.../A-SQL-Server-DBA-myth-a-day-(1930)-TRUNCATE-TABLE-is-non-logged.aspx

Truncate is indeed logged, it just doesn't have to log each individual record.

-Jon

re: SQL Table stored as a Heap - the dangers within

Peter — Mon, 03 May 2010 12:41:46 GMT

To comment on the comments on truncate (all are false).

According to books online:

* Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

* Fewer locks are typically used.

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.

* Without exception, zero pages are left in the table

After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

re: SQL Table stored as a Heap - the dangers within

Danijel — Sun, 02 May 2010 05:56:53 GMT

As far as I know, TRUNCATE TABLE statement actually drops the whole table altoghter, and re-creates it anew.

Twitter Trackbacks for SQL Table stored as a Heap - the dangers within - Mike Diehl's WebLog [asp.net] on Topsy.com

Fri, 30 Apr 2010 19:35:27 GMT

Pingback from Twitter Trackbacks for SQL Table stored as a Heap - the dangers within - Mike Diehl's WebLog [asp.net] on Topsy.com

re: SQL Table stored as a Heap - the dangers within

Darren Kopp — Fri, 30 Apr 2010 19:32:23 GMT

Truncate skips the transaction log, so delete was most likely reading the table info to put info into transaction log, which is why it was taking so long.

re: SQL Table stored as a Heap - the dangers within

shanmugamm — Fri, 30 Apr 2010 19:05:29 GMT

Great

evastonline.com » Blog Archive » Computer programer

evastonline.com » Blog Archive » Computer programer — Sat, 15 Aug 2009 16:29:58 GMT

Pingback from evastonline.com » Blog Archive » Computer programer