We have a database supplied by a third party which contains a "diary" table. The table contains an operator name, an entry time and a diary memo entry column. This column is defined as VARCHAR(1000).
We have noticed during migration to this new system this memo text column has been populated with CHAR(1000) values and so every entry in the table is exactly 1000 characters long, padded with trailing spaces. The actual maximum length of a memo is around
200 characters, not including the trailing space, and many entries are only around 20 characters long. Just to fill you in further, the table contains around 100,000,000 entries. Consequently the extra whitespace is very costly in terms of disk space.
We have updated the column performing an RTRIM() to remove the wasted whitespace. Selects now return the correct data with no trailing blanks.
The issue we have is that the physical size of the table has not changed at all. It is currently taking up around 50GB with most of the space storing space characters.
We have tried a DBCC SHRINK DATABASE but the table has not changed size at all. We were expecting this to release the whitespace, but it looks like it has just rearranged the rows in the pages and not actually released the space within each row.
We have also tried backing up and restoring the database to a test server but thi
View Complete Post