Christian Buettner (9/4/2009)
This was a very nice one! Out of curiosity - did you realize this via testing or is there official info somewhere in BOL?
Thanks, Christian.
The padding of fixed length strings is documented in BOL at http://msdn.microsoft.com/en-us/library/ms186939.aspx and http://msdn.microsoft.com/en-us/library/ms187403.aspx.
The need to rebuild a clustered index to reclaim space held by a dropped column is mentioned in a note on the page on ALTER TABLE: http://msdn.microsoft.com/en-us/library/ms190273.aspx
- After trimming there is no change - you again need to rebuild the index (PK)
Only partly correct. There is no change in the amount of data pages held by the table, but there is lots more free space available. I'm sure there is some DBCC option to report that, but I can't recall off the top of my head. However, this is a difference with the dropped column - that space remains reserved until the index is rebuilt, so adding rows and updating varying-length data with longer lengths will cause extra pages to be allocated. After the TRIM, lots of space is available, scattered throughout the pages, so many INSERT and UPDATE operations will be able to use that space instead of having to allocate new pages.