November 25, 2015 at 11:40 am
I recently migrated a database from a SQL 2005 Server to SQL Server 2012, and as a result can now use features in 2012 and 2008 R2 that were unavailable in 2005. One feature that looked promising was the ability to designate columns as sparse columns. I wrote a script to identify columns that should be set to SPARSE based on the thresholds in the MSDN article https://msdn.microsoft.com/en-us/library/cc280604.aspx
However, after designating the columns returned in my script as SPARSE, the database actually grew in size. At first I thought it could be related to the database files increasing in size due to the overhead associated with converting the columns from standard columns to SPARSE ones, so I tried shrinking the database, and while that helped, it was still larger than the original version of the database with no sparse columns after I ran shrink database on the original version.
Next I thought that the thresholds listed in the article might be incorrect, so I limited the SPARSE columns to only ones that were over 99% NULL and it was the same result as before, after designating columns as SPARSE the database grew in size.
Has anyone had a similar experience? How can the database increase in size if the columns I'm designating as sparse are over 99% NULL?
November 25, 2015 at 1:46 pm
It looks like I may have figured this out. It appears that the space savings for altering existing columns to SPARSE columns is only realized after you rebuild the indices on the affected tables and shrink the database. The savings weren't as much as I had hoped, but at least it was in the right direction.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply