Comments on: Removing Columns

By: Benjamin Lotter

Benjamin Lotter — Mon, 08 Feb 2010 14:59:18 +0000

Thanks for the article; it was new and vital information. The only thing I can’t agree with is the caption on your graphic. For it wasn’t the hair but the Spirit of God that gave Samson power to tear down those columns.
See Judges 16:28-30:
And Samson called unto the LORD, and said, O Lord God, remember me, I pray thee, and strengthen me, I pray thee, only this once, O God, that I may be at once avenged of the Philistines for my two eyes. And Samson took hold of the two middle pillars upon which the house stood, and on which it was borne up, of the one with his right hand, and of the other with his left. And Samson said, Let me die with the Philistines. And he bowed himself with all his might; and the house fell upon the lords, and upon all the people that were therein. So the dead which he slew at his death were more than they which he slew in his life.

By: Michael J. Swart

Michael J. Swart — Sat, 06 Feb 2010 15:12:57 +0000

Mark, Brad, John, thanks for the feedback! It makes blogging worthwhile.

By: John Sansom

John Sansom — Sat, 06 Feb 2010 08:07:31 +0000

This is why I love SQL Server, you really do learn something new every day.

Thanks for sharing.

By: Brad Schulz

Brad Schulz — Fri, 05 Feb 2010 18:41:25 +0000

Excellent stuff, Michael. Really informative.

By: Michael J. Swart

Michael J. Swart — Fri, 05 Feb 2010 18:27:07 +0000

Mark, that’s an excellent question.

A quick check tells me that it’s the same story for heaps as it is for indexed tables. Except for the part about rebuilding indexes:

ALTER INDEX ALL ON NAME REBUILD;

While this command doesn’t fail, it doesn’t have any affect at all (because there are no indexes to rebuild). With heaps, new rows are still huge. Dropped columns are still there. Even if the table has non-clustered indexes against heaps. The dropped columns are still there.

The point is is that the index rebuild does the trick only when a table has a clustered index.

To the problem of reclaiming space used by dropped columns in heaps, I don’t know what the solution is. One workaround is to create a clustered index for it. Even if it is temporary.

By: Mark Vaillancourt

Mark Vaillancourt — Fri, 05 Feb 2010 17:04:49 +0000

It would be interesting to discuss what happens with heaps, too.

By: Mark Vaillancourt

Mark Vaillancourt — Fri, 05 Feb 2010 17:01:46 +0000

Great info, Michael. This is not something I had realized. Thanks.

By: Things I Read This Week | Brent Ozar - Too Much Information

Things I Read This Week | Brent Ozar - Too Much Information — Fri, 05 Feb 2010 13:30:11 +0000

[...] Removing Columns Doesn’t Decrease Table Size – I read the title and I thought Michael Swart was pulling my leg. The fact that I learned something by reading this makes me think I’m not ready for the MCM program. I have moments like this whenever I read a bunch of blog posts in one sitting, and I’m glad Michael blogged this. [...]