DBCC CLEANTABLE is not freeing used space

Question

I have about 300 tables with 5.5kk rows each. One of the rows is using nvarchar(128) as a datatype (SQL Server 2012).
We decided to change this to int and add FK to dictionary table with all nvarchars.

After doing all of it I removed the nvarchar column, but the size of the tables remained the same. I used DBCC CLEANTABLE and rebuilt the indexes to reclaim free space, but size of the table is still not changing.

So far, the only way I found is to copy all data to the new table.

Question: What am I missing here ? Why the space is still marked as used and I can't free is by shrinking or CREANTABLE command?

Thank you!

Answ: Looks like the answer was pretty simple and I was not able to find it due to my lack of knowledge. The main problem here was heap fragmentation. This query worked for me:

ALTER TABLE [TABLE_NAME] REBUILD

I am not sure that it's the best way, but at least it's a working one.

Edited1: Sorry, I think I forgot to mention - I had clustered index on the Text field, so I had to remove the index to be able to actually remove the field. Now I have no indexes.

Edited2:

old table:

CREATE TABLE [dbo].[Data_](
    [ID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Text] [nvarchar](128) NOT NULL,
    [Category] [tinyint] NOT NULL,
    [Country] [nvarchar](2) NOT NULL,
    [ImportTimestamp] [date] NOT NULL
)

new table:

CREATE TABLE [dbo].[Data_New](
    [ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Category] [tinyint] NOT NULL,
    [Country] [nvarchar](2) NOT NULL,
    [TextID] [int] NOT NULL)

ALTER TABLE [dbo].[Data_New]  WITH CHECK ADD FOREIGN KEY([TextID])
REFERENCES [dbo].[Dictionary] ([Id])

Copy script:

INSERT INTO Data_New
      ([Category]
      ,[Country]
      ,[TextID])
SELECT 
      [Category]
      ,[Country]
      ,[TextID]
  FROM Data_

Answer 1

Are you sure the table should get smaller because of this column change? nvarchar(128) doesn't mean SQL Server will allocate 128 bytes (+2) for saving data, for example the string 'test'. The string 'test' will only take 6 bytes. Maybe you need to check free space in table first:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME = 'TABLE_NAME'
    AND t.is_ms_shipped = 0'
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Answer 2

If you don't care about understanding the issue and just want to get rid of it then rebuilding the index is a sure way to remove any waste. This is because a index rebuild constructs the physical data structures freshly. Whatever the old index contained will not matter anymore.

There are trade-offs involved when comparing this with CLEANTABLE. If a rebuild does the job for you I'd always do that because it is such a complete solution.

When I say "index" in this answer I mean all physical structures that contain one of the columns you care about. That can be b-tree indexes or the heap the table is based on.