How do I find which row is using the most space in my database?

Question

I have a SQL Server 2012 database which users can attach files to (mainly Jpgs) however the table size is 130mb with only 20 records.

I need to find out which row is using the most amount of space.

The data is stored as varbinary

Thanks

@LasseV.Karlsen Yes you are correct. Bad Googling Skills for this question. Thank you — SCramphorn, Jun 06 '17 at 14:20

score 2 · Accepted Answer · answered Jun 06 '17 at 14:17

2

You can use DATALENGTH to get the length of the varbinary column:

SELECT Id, Name, DATALENGTH(BinaryColumn)
FROM dbo.YourTable
ORDER BY DATALENGTH(BinaryColumn) DESC

answered Jun 06 '17 at 14:17

marc_s

Thanks, perfect. How can I convert this to kb / mb - * 8 / 1024? `select ((DATALENGTH(filecontent) * 8) / 1024) fg, * from Attachments order by fg desc` – SCramphorn Jun 06 '17 at 14:21
@SCramphorn: `DATALENGTH` returns **number of bytes** - so just divide by 1024 (or 1024.0, if you want to avoid integer division) or by (1024*1024) etc. – marc_s Jun 06 '17 at 14:22

1 Answers1