Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to find out how much disk space a single row is taking up in a table. I can then use this as an approximate to work out how much disk space I can recover by removing old unneeded data. Would I have to combine all of the columns in a single SELECT statement, like so?

SELECT DATALENGTH([Id]) + DATALENGTH([Column1]) + DATALENGTH([Column2]) AS 'Size'
  FROM [MyTable]

Or is there a better, more efficient way that I'm not aware of?

EDIT

I have just noticed that if a column has a NULL value, then I get NULL as the result.

share|improve this question
    
possible duplicate of SQL Server Row Length –  Aaron Bertrand Feb 8 '12 at 12:19
1  
Also see sqlskills.com/BLOGS/KIMBERLY/post/… –  Aaron Bertrand Feb 8 '12 at 12:23
    
See [this question][1] on SO for some suggestions. [1]: stackoverflow.com/questions/227438/sql-server-row-length –  Rory Feb 8 '12 at 23:04
add comment

migrated from stackoverflow.com Feb 8 '12 at 15:09

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

How much data do you have?

Personally, I'd do one of

  • try it on a test system and check "before" and "after"
  • just remove unused data because by definition, it isn't needed

The questions is complicated when you have

  • variable length data: how much of the field is used on average?
  • page density (rows per page) may not increase therefore you save no space
  • indexes duplicate a column value, so you have to count some ones twice

So, I'd do it empirically rather than by calculation...

share|improve this answer
    
IMO best method to get what OP is after is to look at the table as a whole...sp_spaceused to get total footprint/rowcount = avg space per row. –  JNK Feb 8 '12 at 22:08
    
@JNK: er.. good point. I overlooked the obvious there... –  gbn Feb 9 '12 at 10:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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