CODEWITHSUNDEEP
sql-server-2008
Shai Cohen
Shai Cohen

Reputation: 6249

Calculate size in megabytes from SQL varbinary field

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?

Upvotes: 34

Views: 67511

Answers (2)

Bryan__T
Bryan__T

Reputation: 123

A friendly update to economize the solution query, for any who come across this post:

  • only sums the datalength once
  • avoids repetitive division and joining
select bytes
    , bytes / 1024.0 as kilobytes
    , bytes / power(1024.0,2) as megabytes
    , bytes / power(1024.0,3) as gigabytes
    , bytes / power(1024.0,4) as terabytes
from
(
    select sum(datalength(details)) as bytes
    from supportContacts
) a

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171529

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 |

Upvotes: 85

Related Questions