Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fixed to varying length


Fixed to varying length

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
Comments posted to this topic are about the item Fixed to varying length


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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
elbedata
elbedata
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 Visits: 1087
I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...:-)

Lars Broberg
Elbe-Data AB
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
That was a good one. Guess DBCC Cleantable also wouldn't help in this case.

"Keep Trying"
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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...


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
elbedata
elbedata
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 Visits: 1087
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
elbedata
elbedata
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 Visits: 1087
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search