Find out how much storage a row is taking up in the database

Question

Is there a way to find out how much space (on disk) a row in my database takes up?

I would love to see it for SQL Server CE, but failing that SQL Server 2008 works (I am storing about the same data in both).

The reason I ask is that I have a Image column in my SQL Server CE db (it is a varbinary[max] in the SQL 2008 db) and I need to know now many rows I can store before I max out the memory on my device.

Answer 1

You can only make rough guesses - there is no exact answer to the question "how many rows I can store before I max out the memory on my device" since you do not have exclusive use of your device - other programs take resources too, and you can only know how much storage is available at the present time, not at some time in the future. Additionally, your images are likely compressed and therefore take variable amounts of RAM.

For guessing purposes, simply the size of your image is a good approximation of the row size; the overhead of the row structure is negligible.

Answer 2

A varbinary(max) column could potentially contain up to 2GB of data by itself for each row. For estimated use based on existing data, perhaps you could do some analysis using the DATALENGTH function to work out what space a typical one of your images is taking up, and extrapolate from there.

Answer 3

Maybe not the 100% what you wanted but if you want to know how much size an Image take just do

SELECT [RaportID]
                      ,DATALENGTH([RaportPlik]) AS 'FileSize'
                      ,[RaportOpis]
                      ,[RaportDataOd]
                      ,[RaportDataDo]
FROM [Database]

Any other additional counting you need to do yourself (as in prediction etc).