Hi Sergey,

I created 2 sample tables with identical fields namely fname, mname, lname.
For table 1, I set the fields to char(25) and for table2, I set the fields to varchar(25)

I then created a program that will populate each table with 300,000 thousand records(fname= 'MICHAEL'; mname = 'D', lname = 'ZAPANTA')

Here are the records returned when I executed sp_spaceused

For Table1:
Name = Table1
Rows = 300000
Reserved = 26120 KB
Data = 26088 KB
Index_Size = 8 KB
Unused = 24 KB

For Table2:
Name = Table2
Rows = 300000
Reserved = 9664 KB Data = 42296 KB
Index_Size = 8 KB
Unused = 48 KB

I then alter the fields of table1 to change their datatype to varchar(25). After altering, I executed sp_spaceused again.
Below are the results

For Table1:
Name = Table1
Rows = 300000
Reserved = 53768 KB
Data = 53720 KB
Index_Size = 8 KB
Unused = 40 KB

Notice the increase in size.

Then I updated the records using the following statements to trim leading spaces:
1. Update table1 set fname = rtrim(fname)
2. Update table1 set mname = rtrim(mname)
3. Update table1 set lname = rtrim(lname)


I re executed sp_spaceused and the results were the following:

For Table1:
Name = Table1
Rows = 300000
Reserved = 53768 KB
Data = 53720 KB
Index_Size = 8 KB
Unused = 40 KB

The space being consumed by table1 was not released.

How can I set SQL Server to automatically released unused spaces?

Please advice... Thanks in advance...