Converting Bytes to KB, MB or GB

I was looking for some algorithm on how to convert Bytes to KB or MB or GB depending on the value. Most of the algorithm that I saw are using case statement to implement the breakdown as brackets.

Here's my own version. This will generate some random number that will be converted to KB, MB or GB. But this should help me in the future.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint) 
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = coalesce(nullif(ByteSize/power(cast(1024 as bigint),4),0),
                        nullif(ByteSize/power(cast(1024 as bigint),3),0),
                        nullif(ByteSize/power(cast(1024 as bigint),2),0),
                        nullif(ByteSize/power(cast(1024 as bigint),1),0), 
                        ByteSize
                        ),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)), 
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)), 
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)), 
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)), 
                   'Bytes'
                  )
from SizeInStorage

Here's a sample result set:

ByteSize             Formatted       Converted   Unit
-------------------- --------------- ----------- -----
926528091            926,528,091     883         MB 
105563351            105,563,351     100         MB 
1603808599           1,603,808,599   1           GB 
866882480            866,882,480     826         MB 
426406913            426,406,913     406         MB 

Update...I want to convert bytes with two decimal places.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,5)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,3)
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = convert(varchar(30), isnull(cast(ByteSize as money)/ cast(coalesce((nullif((ByteSize/power(cast(1024 as bigint),4)),0)/nullif((ByteSize/power(cast(1024 as bigint),4)),0)) * power(cast(1024 as bigint),4),
                        (nullif((ByteSize/power(cast(1024 as bigint),3)),0)/nullif((ByteSize/power(cast(1024 as bigint),3)),0)) * power(cast(1024 as bigint),3),
                        (nullif((ByteSize/power(cast(1024 as bigint),2)),0)/nullif((ByteSize/power(cast(1024 as bigint),2)),0)) * power(cast(1024 as bigint),2),
                        (nullif((ByteSize/power(cast(1024 as bigint),1)),0)/nullif((ByteSize/power(cast(1024 as bigint),1)),0)) * power(cast(1024 as bigint),1)) as money)                                               
                        ,
                        ByteSize * 1.00
                           ), 1),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)),
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)),
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)),
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)),
                   'Bytes'
                  )
from SizeInStorage
 

Here's the resultset:

ByteSize      Formatted         Converted   Unit
------------  ----------------- ----------- ----
1582690891    1,582,690,891     1.47         GB
786337767     786,337,767       749.91       MB
431093153     431,093,153       411.12       MB
1067677070    1,067,677,070     1,018.22     MB
626880896     626,880,896       597.84       MB
28760         28,760            28.09        KB
308           308               308.00       Bytes

Comment?


~~ CK

Getting Size of All Tables in The Database

Here's a quick way of getting the size of all tables in the database. This will display the size in pages, bytes and the number of rows.

select 
   o.name,
   reservedpages = sum(a.total_pages),
   usedpages = sum(a.used_pages),
   pages = sum(case 
                  when a.type <> 1 then a.used_pages
                  when p.index_id < 2 then a.data_pages 
                  else 0 
               end),
   bytes = SUM(a.used_pages)*8096,
   rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
   inner join sys.partitions p on p.object_id = o.object_id
   inner  join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U' 
group by o.name
order by 3 desc 

Here's the result set:

name           reservedpages  usedpages  pages   bytes  rows
-------------- -------------- ---------- ------- ------ ------
MyTable        4              4          2       32384  176
MyOtherTable   2              2          1       16192  30

Here's another way of doing it.

SELECT
   TableName = QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM sys.objects AS o
   INNER JOIN sys.partitions AS p ON o.object_id = p.object_id
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name
ORDER BY [TableName]

Here's the result set:

TableName                            Rows
------------------------------------ ------
[dbo].[Table1]                          176
[dbo].[Table2]                           30

And to find the size of a specific table

SELECT
   TableName = QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM tempdb.sys.objects AS o
   INNER JOIN tempdb.sys.partitions AS p ON o.object_id = p.object_id and
                                    o.object_id = object_id('[TableName]')
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name

As always, comments are welcome.


~~ CK

How do you get the actual size of a Database?

How do you get the actual size of SQL Server Database? Quick answer: execute a sp_spaceused command. However, I found this excerpt from Books Online "When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections…..There are some situations, for example, after an index is dropped, when the space information for the table may not be current." In short, sp_spaceused is not always accurate.

For more accurate calculation of database size, try the following code:

select 
cast(size/128.0 as numeric(10,1)) as [size-mb], 
cast(FILEPROPERTY(name, 'spaceused')/128.0 as numeric(10,1)) as [used-mb], 
maxsize [maximum size-mb], 
cast(growth*8/1024 as varchar(11))+
 case when status&0x100000>0 then '%' else '' end [growth], 
isnull(filegroup_name(groupid),'log') [groupname], 
cast(100*(size/128-(FILEPROPERTY(name,'spaceused')/128))/(size/128.0)
   as int) [% free]
from sysfiles

Here's the result set:

size-mb  used-mb  maximum size-mb growth   groupname  % free
-------- -------- --------------- -------- ---------- -----------
500.0    10.4     -1              64       PRIMARY    98
58.0     5.3      268435456       16       log        91

~~CK