SQL Server does not automatically defragment tables/databases. If the table in question has clustered index than DBCC INDEXDEFRAG or DBCC DBREINDEX can be run to defragment it. If the table does not have clustered index, you can eliminate such fragmentation by temporarily adding a clustered index or by unloading and reloading the data.

>
>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')
>
<snip>
>
>The space being consumed by table1 was not released.
>
>How can I set SQL Server to automatically released unused spaces?