Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Fixed to varying length Expand / Collapse
Author
Message
Posted Thursday, September 3, 2009 8:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
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
Post #782693
Posted Friday, September 4, 2009 1:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #782758
Posted Friday, September 4, 2009 1:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 13, 2014 8:35 AM
Points: 3,926, Visits: 1,087
I thought SQL Server was "smart enough" to remove the trailing spaces. You always learn something new here...

Lars Broberg
Elbe-Data AB
Post #782759
Posted Friday, September 4, 2009 2:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
That was a good one. Guess DBCC Cleantable also wouldn't help in this case.

"Keep Trying"
Post #782764
Posted Friday, September 4, 2009 2:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
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
Post #782771
Posted Friday, September 4, 2009 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
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
Post #782772
Posted Friday, September 4, 2009 2:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
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
Post #782773
Posted Friday, September 4, 2009 3:33 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 13, 2014 8:35 AM
Points: 3,926, Visits: 1,087
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
Post #782786
Posted Friday, September 4, 2009 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
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
Post #782793
Posted Friday, September 4, 2009 4:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 13, 2014 8:35 AM
Points: 3,926, Visits: 1,087
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
Post #782796
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse