SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / Fixed to varying length / Latest Posts

RE: Fixed to varying length

SQL Writer — Wed, 28 Jul 2010 08:03:47 GMT

Good question and best answer.i never take any idia about this type problems.+thanks

RE: Fixed to varying length

Tush — Wed, 02 Jun 2010 23:51:03 GMT

A simple but head scratching Question...! :crazy:Many thanks.I learnt something new today ! :-)

RE: Fixed to varying length

john.arnott — Tue, 22 Sep 2009 23:47:33 GMT

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 [b]and[/b] 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.

RE: Fixed to varying length

The Dixie Flatline — Sat, 05 Sep 2009 14:31:19 GMT

Good question and a good reminder.

RE: Fixed to varying length

Aaron N. Cutshall — Fri, 04 Sep 2009 06:51:34 GMT

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:

RE: Fixed to varying length

sjimmo — Fri, 04 Sep 2009 06:49:08 GMT

Excellent question - made me think, got it wrong, then learned something.

RE: Fixed to varying length

RichB — Fri, 04 Sep 2009 04:48:51 GMT

Tricksy tricksy.Good question, good follow up. Learned something new today.ta

RE: Fixed to varying length

elbedata — Fri, 04 Sep 2009 04:03:57 GMT

[quote][b]Hugo Kornelis (9/4/2009)[/b][hr][quote][b]elbedata (9/4/2009)[/b][hr][quote]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... [/quote]I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?[/quote]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 [i]some[/i] 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: [b]I[/b] 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.[/quote]ok - I'm not arguing against this...

RE: Fixed to varying length

Hugo Kornelis — Fri, 04 Sep 2009 03:47:46 GMT

[quote][b]elbedata (9/4/2009)[/b][hr][quote]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... [/quote]I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?[/quote]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 [i]some[/i] 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: [b]I[/b] 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.

RE: Fixed to varying length

elbedata — Fri, 04 Sep 2009 03:33:14 GMT

[quote]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... [/quote]I get your point (and it is of course valid), but if you really need the trailing spaces, why change the datatype?

RE: Fixed to varying length

Hugo Kornelis — Fri, 04 Sep 2009 02:37:16 GMT

[quote][b]Chirag (9/4/2009)[/b][hr]That was a good one. Guess DBCC Cleantable also wouldn't help in this case.[/quote]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.

RE: Fixed to varying length

Hugo Kornelis — Fri, 04 Sep 2009 02:35:10 GMT

[quote][b]elbedata (9/4/2009)[/b][hr]I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...:-)[/quote]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...

RE: Fixed to varying length

Hugo Kornelis — Fri, 04 Sep 2009 02:33:39 GMT

[quote][b]Christian Buettner (9/4/2009)[/b][hr]This was a very nice one! Out of curiosity - did you realize this via testing or is there official info somewhere in BOL?[/quote]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[quote] - After trimming there is no change - you again need to rebuild the index (PK) [/quote]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.

RE: Fixed to varying length

ChiragNS — Fri, 04 Sep 2009 02:09:03 GMT

That was a good one. Guess DBCC Cleantable also wouldn't help in this case.

RE: Fixed to varying length

elbedata — Fri, 04 Sep 2009 01:48:36 GMT

I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...:-)

RE: Fixed to varying length

Christian Buettner-167247 — Fri, 04 Sep 2009 01:48:36 GMT

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:[code]name rows reserved data index_size unused Status+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Table1 786432 164984 KB 164416 KB 544 KB 24 KB Original DataTable1 786432 329952 KB 329368 KB 552 KB 32 KB Change to varcharTable1 786432 340728 KB 170048 KB 312 KB 170368 KB 1st PK rebuildTable1 786432 340728 KB 170048 KB 312 KB 170368 KB Trim DataTable1 786432 120152 KB 59920 KB 136 KB 60096 KB 2nd PK Rebuild[/code] 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)

Fixed to varying length

Hugo Kornelis — Thu, 03 Sep 2009 20:37:20 GMT

Comments posted to this topic are about the item [B]Fixed to varying length[/B]