table size issue

Question

table size issue

Amit Pandey - Scalability Engineers

Old Hand

Points: 355
More actions
October 6, 2010 at 6:10 am

#97340

Hi All,
I was trying to optimize an enviorment and saw that some of the column in a very large table are only 0 and 1.
I thought to optimize the design of the table by chanding the datatype from int to bit. Since I understand the application thoroughly I know this wont cause any issue functionally as the stored proc which inserts records would be inserting only 0s and 1s.
The issue here is that after I change the datatype, I see the table size increased instead of decreasing. I tried to replicate the issue and again I found that the table size increases instead of decreasing. However If I create a new table and then instead of altering the existing one it works and is smaller in size.
Example:
SQL Server 2005, standard edition.
---------------------------------
Create table testspace1
(colA int,
colB int)
GO
insert into testspace1 select 1, id from sysobjects
go
insert into testspace1 select * from testspace1
go 10
GO
sp_spaceused testspace1
when we execute the above script, it shows the size as
namerowsreserveddataindex_sizeunused
testspace11239042120 KB2088 KB8 KB24 KB
now I execute the script to alter the datatype and then check the size again
alter table testspace1 alter column colA bit
GO
sp_spaceused testspace1
We can see that the table is larger is size now.
namerowsreserveddataindex_sizeunused
testspace11239042696 KB2672 KB8 KB16 KB
have anybody encountered such issue. Any suggestions on reclaiming the table space without creating a new table will be appreciated.
Amit Pandey
SQL Server DBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 1

If I remember correctly, a "change" doesn't rebuild the table. It adds the field to the end of the fixed size columns on each page, and marks the old column as unused. However the data isn't removed. Meaning you have dead data stuck in the middle of each row.

A rebuild of the table cleans this up.

Amit Pandey - Scalability Engineers Old Hand Points: 355 More actions · Answer 2

Okay, so recreating the table is the only way to claim space.

Sounds like a bug in the product to me.

I think this should have been at least documented in the MSDN

tommyh SSCertifiable Points: 6302 More actions · Answer 3

Amit Pandey DeBugSQL (10/8/2010)
Okay, so recreating the table is the only way to claim space.
Sounds like a bug in the product to me.
I think this should have been at least documented in the MSDN

Well if it had been a Varchar column then DBCC CLEANTABLE http://msdn.microsoft.com/en-us/library/ms174418%28v=SQL.90%29.aspx.

And hardly a bug. Performance is key to SQL (not disk space) and rebuilding tables because of dropped columns aint exactly good if you have a table with several million rows worth of data (and GB worth of data).

/T

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 4

I would agree this is not a bug. What you are doing is done very, very rarely. Therefore it's not something that you necessarily would want to impact performance by rebuilding the table if you were not affecting it.

I agree that better documentation would make sense. You can submit a note to the ALTER DATABASE page on MSDN.