4

I have a huge table in SQL Server 2005. (The table is about 40gb) I am looking for the safest and most pain-free way to either free up space, or delay its growth.

The table has three fixed length (char(10)) columns that are a) completely unneeded and b) almost totally filled with NULL.

If I drop these columns will SQL Server start using the freed space within the data file when new records are imported? Or does their fragmented nature (I assume) because of being spread out over many records mean that SQL Server will ignore the space for performance reasons?

If I do drop them, can I run shrink database to free up the space?

I'm the only developer who utilizes this database, and I know these columns are surplus to requirements. They were added along with every other column in a flat file 15 years ago when different lazy developers created this database.

Improve this question
0

1 Answer 1

Reset to default
7
  1. Dropping them will reclaim space if you rebuild indexes or run DBCC CLEANTABLE
  2. New rows will not have these columns
  3. Fixed length columns always have space reserved whether NULL or not (note: variable length columns with NULL don't consume space usually)
  4. Don't shrink the database, it will only grow again and has other issues (out of scope here)
  5. 40GB is not big

Note: an index rebuild to reclaim space assumes you have a clustered index

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.