December 2, 2008 at 2:22 pm
Hi All,
I recently removed 8 or so unused columns from a table with well over 100 million rows. This table had a clustered index so once the unused columns were removed, I rebuilt the clustered index and according to the 'Data Space' value in the table Properties (using SSMS GUI) the data space dropped by 2 gigs. Nice!
Then, I moved on to a different table that had no clustered indexes. Once I removed 5 unused columns and rebuilt the non-clustered index, the 'Data Space' value didn't move even .001 MB. Is this possible? Or is something else going on that I need to tend to in order to free up that wasted space?
Any help is kindly appreciated.
Thanks,
Nate
December 2, 2008 at 2:49 pm
It's not the difference between whether it has a clustered index or not. Some tables gain more than others from removing unused columns. A lot of it depends on the data type of the columns, and their nullability.
As an aside, it's generally best to have a clustered index on any table that's going to have indexes on it at all. Can significantly reduce the disk space used and speed up queries, in most cases. You might want to look at options on that for the second table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2008 at 2:56 pm
nate (12/2/2008)
Then, I moved on to a different table that had no clustered indexes. Once I removed 5 unused columns and rebuilt the non-clustered index, the 'Data Space' value didn't move even .001 MB. Is this possible? Or is something else going on that I need to tend to in order to free up that wasted space?
The clustered index is the table, so rebuilding that will change the space allocation. On tables without a cluster, the data is in a structure called a heap. Since you didn't rebuild that (and there's no way to do so in 2005) the space didn't change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 3:02 pm
I was wondering if that was the case (regarding the heap table).
Thanks so much for the responses!
Nate
December 2, 2008 at 5:14 pm
One thing you can try is adding a clustered index to those tables. Once the clustered index has been built, you could then remove it if you really need the table setup as a heap.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 2, 2008 at 6:14 pm
Try to create a new table with the same structure and move the data from old tables into new, this should hlep you reclaim the space.
Check this and see, if it helps your need.
Maninder
www.dbanation.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply