Chapter 30 – Reusing space in a table

It’s rare that I read the first paragraph in a chapter of a technical book and smile with fond memories. 

This is one of those rare times.  If you have the book SQL Server MVP Deep dives, written by Joe Webb (Blog | Twitter) then you know what I’m talking about.  If you don’t have the book and you are reading this… well, uh, go.  get the book.  Here. Today. 

This is a cool chapter… while I have only used the functionality provided by this on a couple of occasions, I can attest to its value.  I can especially attest to its value for some C.O.T.S. packages out there that update their schema with a version upgrade and the space is not readily re-used.  I’m not going to name names because that’s not the point… the point is that if you are a developer for some of the C.O.T.S. implementations out there; please keep this in mind.  It’s an easy, easy thing to be on the lookout for and it will keep grumpy DBA’s such as myself from calling you.

More to the point, if you use SQL Server to store data in some of the larger data types (xml, text, image, etc…) and you have data that falls outside of the IN_ROW_DATA allocation space, this chapter and understanding how SQL Server cleans up and doesn’t clean up after itself is very, very important.

The only other item that I think bears a mention is that this will lock your table while this process is running.  That’s a pretty important thing to keep in mind if you are thinking about running this command against your Transactions table in production.  🙂

Lastly, for those of you who haven’t frequented Joe’s blog, you should.  I spent about 45 minutes on it while trying to write this little post… he has some great content!  http://webbtechsolutions.com/blog/


0 Responses to “Chapter 30 – Reusing space in a table”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers