SQL Server table size increased with column data type change I have a SQL Server table with 39 million rows. I changed the data type of existing columns as follows:


Col A -> tinyint -> bit
Col B -> tinyint -> bit
Col C -> tinyint -> bit
Col D -> tinyint -> bit
Col E -> tinyint -> bit
Col F -> smallint -> tinyint
Col G -> smallint -> tinyint


The table size was supposed to have shrunk, however, the size increased from 11gb to 18gb.

I tried the following:


Rebuild all indexes
DBCC SHRINKDATABASE ('DBName')


however it did not work.

Any idea where to look for the problem?
We need more info. Stuff like:

Is the table clustered or a heap?

What indexes do you have?

How did you do the change? ALTER TABLE ALTER COLUMN, or use some GUI?

If you used ALTER TABLE, can you post those commands?

If you used some GUI, what SQL did it generate?

For instance, I did a test. I had a heap, and used ALTER TABLE ALTER COLUMN. Evidently, the old data was still there for the tinyint -> bit change. This means that the row lengh increased, and subsequently more space was needed. Also, since it was a heap, forwarding pointers were left at the old row locations, for the rows that were moved. Why was the old data still there? One would have to ask MS. Sometimes they are clever, and for instance delay a change until you actually modify data, so it can do it as a a meta-data only change. 

The smallint -> tinyint didn't change number of bytes/pages. This was indeed done as a meta-data only change.

So, in order to examine your case, we need a repro from you, with your table structure including stuff like I mentioned above (heap or clustered table, other indexes etc).

For the record, since my table was a heap, I could reclaim the space by doing an ALTER TABLE REBUILD. Probably easier and faster than creating a new table.

Here's the script I used, with some comments. In case you want to dig deeper.

USE tempdb

SET STATISTICS IO ON

DROP TABLE IF EXISTS x1

SELECT TOP(100000)
 CAST(0 AS tinyint) AS a
,CAST(0 AS tinyint) AS b
,CAST(0 AS tinyint) AS c
,CAST(0 AS tinyint) AS d
,CAST(0 AS tinyint) AS e
,CAST(0 AS tinyint) AS f
,CAST(0 AS tinyint) AS g
,CAST(0 AS tinyint) AS h
,CAST(0 AS tinyint) AS i
,CAST(0 AS smallint) AS j
INTO x1
FROM sys.columns AS a, sys.columns AS b

--Verify data types
EXEC sp_help x1

--Below proc can be found at my web-site, https://karaszi.com/spindexinfo-enhanced-index-information-procedure
EXEC sp_indexinfo 'x1' --266 pages

ALTER TABLE x1 ALTER COLUMN a bit
EXEC sp_indexinfo 'x1' --306 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN b bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above

ALTER TABLE x1 ALTER COLUMN c bit
ALTER TABLE x1 ALTER COLUMN d bit
ALTER TABLE x1 ALTER COLUMN e bit
ALTER TABLE x1 ALTER COLUMN f bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above



ALTER TABLE x1 ALTER COLUMN g bit
EXEC sp_indexinfo 'x1' --346 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN h bit
EXEC sp_indexinfo 'x1' --346 pages

ALTER TABLE x1 ALTER COLUMN i bit
EXEC sp_indexinfo 'x1' --378 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN j tinyint
EXEC sp_indexinfo 'x1' --378 pages, above was meta-data only operation


SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('x1'), NULL, NULL, 'DETAILED')
--Pages are full

--Lets look at a row
DBCC TRACEON(3604)

SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('x1'), null, null, 'DETAILED') AS a
WHERE page_type_desc = 'DATA_PAGE'
--Grab some page number from above, the column allocated_page_page_id

--Check out the page
--Note all dropped columns, they are still there
DBCC PAGE('tempdb', 1, 268, 3)
--Also, we got forwarded records because of the row length increased

--Rebuild the data
ALTER TABLE x1 REBUILD
EXEC sp_indexinfo 'x1' --170 pages, data reclaimed
DBCC PAGE('tempdb', 1, 544, 3)