Calculate size in megabytes from SQL varbinary field

Question

We have a db table that stores files as varbinary(MAX). When we run the following script:

SELECT SUM(LEN(Content)) FROM dbo.File

The result is:

35398663

I want to convert this number into megabytes? Is this possible?

Solution

Use DATALENGTH to retrieve the number of bytes and then convert, like this:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE supportContacts 
    (
     id int identity primary key, 
     type varchar(20), 
     details varchar(30)
    );

INSERT INTO supportContacts
(type, details)
VALUES
('Email', '[email protected]'),
('Twitter', '@sqlfiddle');

Query 1:

select *, gigabytes / 1024.0 as terabytes
from (
  select *, megabytes / 1024.0 as gigabytes
  from (
    select *, kilobytes / 1024.0 as megabytes
    from (
      select *, bytes / 1024.0 as kilobytes
      from (
        select sum(datalength(details)) as bytes
        from supportContacts       
      ) a
    ) b  
  ) c
) d

Results:

| bytes | kilobytes |     megabytes |      gigabytes |         terabytes |
|-------|-----------|---------------|----------------|-------------------|
|    29 |   0.02832 | 0.00002765625 | 2.700805664e-8 | 2.63750553125e-11 |