Calulating savings between two schemes I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same amount of data between the two schemes but it just seems too good to be true.

Am I calculating or going about this incorrectly?

First I'm using this query to determine size of the tables, so its possible this is the problem.

There is a total of 88324 rows. 1187 are considered root posts and have values for Title, ProductId, TotalViews and have a NULL for ParentPostId. 87137 are considered replies and have NULL for Title, ProductId, TotalViews and have a value for ParentPostId.

At any rate the query, linked to above, outputs the following results in Kilobytes which equates to a 45% improvement which seems to big of an improvement to me.

+---------+--------+
|Current  |Future  |
+---------+--------+
|94,936   |42,784  |
+---------+--------+

My table structure went from a current state of:



to future state of:
Turned out that the query I was using to calculate space combines indexes and data sizes into one. I used the sp_spaceused procedure as well as the below query to track down the the vast size discrepancy to two very large indexes on the current state table that I think were created by the tuning adviser. Those two indexes accounted for 60Mb!!, twice the size of the actual data, lol.

Without those huge indexes current state performs horribly while on the other hand future state doesn't require any additional indexes to perform as fast if not faster. Future state only added about 1.7Mb increase in storage needs which is far below the current states requirements.

query to find index sizes:

SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB 
FROM sys.dm_db_partition_stats AS s 
JOIN sys.indexes AS i 
ON s.[object_id] = i.[object_id] 
AND s.[index_id] = i.[index_id] 
GROUP BY i.[name] 
ORDER BY i.[name] 
GO


result shows two huge indexes:



sp_spaceused 'Post_Old' result confirms that the those two indexes account for the discrepancy: