September 3, 2009 at 8:37 pm
Comments posted to this topic are about the item Fixed to varying length
September 4, 2009 at 1:48 am
This was a very nice one! Out of curiosity - did you realize this via testing or is there official info somewhere in BOL?
Here is some stats for those interested:
name rows reserved data index_size unused Status
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Table1 786432 164984 KB 164416 KB 544 KB 24 KB Original Data
Table1 786432 329952 KB 329368 KB 552 KB 32 KB Change to varchar
Table1 786432 340728 KB 170048 KB 312 KB 170368 KB 1st PK rebuild
Table1 786432 340728 KB 170048 KB 312 KB 170368 KB Trim Data
Table1 786432 120152 KB 59920 KB 136 KB 60096 KB 2nd PK Rebuild
Interesting here:
- After changing to varchar, space is doubled as already mentioned
- After 1st PK rebuild, the "reserved" space has increased again - probably due to some temporary actions.
- After 1st PK rebuild, "data" has gone back to almost original size, but still has more space used than with char - I would assume this is from the 2 byte var-overhead?
- After trimming there is no change - you again need to rebuild the index (PK)
- After the 2nd PK rebuild, reserved and data decrease drastically (data to the approx. expected 30+x percent)
Best Regards,
Chris Büttner
September 4, 2009 at 1:48 am
I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...:-)
Lars Broberg
Elbe-Data AB
September 4, 2009 at 2:09 am
That was a good one. Guess DBCC Cleantable also wouldn't help in this case.
"Keep Trying"
September 4, 2009 at 2:33 am
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.
September 4, 2009 at 2:35 am
elbedata (9/4/2009)
I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...:-)
You mean, when changing the datatype from fixed length to varying length?
I'd hesitate to describe an unasked for removal of trailing spaces "smart". There are builtin functions to remove them on demand, but no builtin functions to easily restore them after they have been removed...
September 4, 2009 at 2:37 am
Chirag (9/4/2009)
That was a good one. Guess DBCC Cleantable also wouldn't help in this case.
Thanks Chirag,
According to BOL, DBCC Cleantable "Reclaims space from dropped variable-length columns in tables or indexed views."
Since in this case a fixed length column is dropped, I'd agree with your guess. I didn't try it, though.
September 4, 2009 at 3:33 am
You mean, when changing the datatype from fixed length to varying length?
I'd hesitate to describe an unasked for removal of trailing spaces "smart". There are builtin functions to remove them on demand, but no builtin functions to easily restore them after they have been removed...
I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?
Lars Broberg
Elbe-Data AB
September 4, 2009 at 3:47 am
elbedata (9/4/2009)
You mean, when changing the datatype from fixed length to varying length?
I'd hesitate to describe an unasked for removal of trailing spaces "smart". There are builtin functions to remove them on demand, but no builtin functions to easily restore them after they have been removed...
I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?
In this case, I don't need them and I'll free up the space by explicitly trimming the data after the change.
But SQL Server doesn't know. It might also be that I'll trim some of the data but not all. Or that the trailing spaces have to be preserved on existing data but new data coming in will be trimmed. Or (...)
That's what I meant with my previous post: I can trim easily if I want to, but I can't easily "untrim" if SQL Server would do that automatically. And since SQL Server doesn't know what I want, it should stay on the "safe" path and not make irreversible changes I don't ask for.
September 4, 2009 at 4:03 am
Hugo Kornelis (9/4/2009)
elbedata (9/4/2009)
You mean, when changing the datatype from fixed length to varying length?
I'd hesitate to describe an unasked for removal of trailing spaces "smart". There are builtin functions to remove them on demand, but no builtin functions to easily restore them after they have been removed...
I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?
In this case, I don't need them and I'll free up the space by explicitly trimming the data after the change.
But SQL Server doesn't know. It might also be that I'll trim some of the data but not all. Or that the trailing spaces have to be preserved on existing data but new data coming in will be trimmed. Or (...)
That's what I meant with my previous post: I can trim easily if I want to, but I can't easily "untrim" if SQL Server would do that automatically. And since SQL Server doesn't know what I want, it should stay on the "safe" path and not make irreversible changes I don't ask for.
ok - I'm not arguing against this...
Lars Broberg
Elbe-Data AB
September 4, 2009 at 4:48 am
Tricksy tricksy.
Good question, good follow up.
Learned something new today.
ta
September 4, 2009 at 6:49 am
Excellent question - made me think, got it wrong, then learned something.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
September 4, 2009 at 6:51 am
I should not have made the assumption that the question was solely about the differences between char and varchar! I was assuming (see, that was my problem!) that the appropriate clean-up would be taken into consideration when I said that it would result in a 70% reduction. In that case, I would have been correct.
Werry twicky, you wascally wabbit! :smooooth:
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
September 5, 2009 at 2:31 pm
Good question and a good reminder.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 22, 2009 at 11:47 pm
Hugo,
Thank you for this gleaming nugget. Although I'm a developer, not a DBA, I like to know something of what's under the hood with the tools we use. This QOD gave us double our money's worth -- an explicit rtrim() is necessary to fully take advantage of the move to varchar and the clustered index must be rebuilt to free the space used by the old fixed length column definition.
I've never been happier to get an answer wrong than this time -- really learned something valuable.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply