SQL 2000:
how do I reclaim free space in data after changing fields from NVARCHAR to VARCHAR?
I have the following:
2 identical tables (tbl1, tbl2) of one column of data for testing purposes - one created as varchar(100), tbl2 with nvarchar(100), and both with a string of 7 chars (like 'aaaaaaa') - each with same number of rows.
ran sp_spaceused on both, confirmed tbl2 took up about 35% more space (1760 vs 1304)
I then ALTER TABLE on tbl2 to amend the data type to varchar, expecting a drop in data size.
nothing
i checked DATALENGTH had changed from 14 to 7 on tbl2, so there were no "hidden" chars being stored. I ran DBCC CLEANTABLE - made no difference.
I tried DBCC SHRINKDATABASE and DBCC SHRINKFILE - also made no difference.
I even tried creating a clustered index, running DBCC INDEXDEFRAG and then dropping the index - also no difference.
DBCC SHOWCONTIG reports a page density of around 73% for tbl2 - tbl1 has over 98% - so how do i get the db to shift the space around to fill the pages, and remove the empty ones, and give me my space back?!
Start Free Trial
