UPDATED: SQL Function - udf_ConvertBytes - Converts any Byte-based unit of measure to any other Byte-based unit of measure (Bytes, MB, GB, TB, PB, EB, ZB, YB, BB, GeopBytes)

Yesterday I wrote THIS article to convert between different byte-based units of measure...in it I wrote that the ideal way to convert is to have a lookup table.  I was wrong.  Wrong, wrong, wrong.  At least from a performance perspective.  Sure, it made the function a one-liner and super easy to understand, and sure, overall the cost was incrediby low and it was still incredibly fast.  The problem is, compared to a giant CASE statement (yuck, it hurts to even say it), it takes 3 times as long to execute.  Now, 3ms instead of 1ms doesn't seem like much, but if you're running this function against a column in a table with a lot of rows, that extra 2ms is going to add up.  (Think about 2 ms an extra 1 million times)

So, I've updated this function to use a bunch of giant CASE statements so all the logic is tied up in the function and the function has no dependency on any outside data source.  It's dozens of lines now, and LOOKS like it would be a lot more expensive or time-consuming, but the SQL engine only really has to make 3 logic decisions and then some math without going out to an external data source like a table.  Oh well.

USE [YourDB]
GO


-- CREATE THE FUNCTION TO DO THE CONVERSION
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Number2 (John Nelson) -
http://number2blog.com
-- Create date: 2009-07-02
-- Description: Convert bytes from one byte-based unit of measure
--              (Bytes, KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes)
--              to any other byte-based unit of measure in human-
--              readable format...meaning the output is a number
--              followed by a UOM, not just a number.
--              example: 21.409023 GB
-- INPUT: @InputNumber - Decimal(38,7)
--        @InputUOM - VARCHAR(11)
--        @OutputUOM - VARCHAR(11)
--
-- OUTPUT: VARCHAR(64)
--
-- =============================================
CREATE FUNCTION [dbo].[udf_ConvertBytes]
(
   @InputNumber   DECIMAL(38,7),
   @InputUOM      VARCHAR(11) = 'Bytes',
   @OutputUOM     VARCHAR(11) = 'Gigabytes'
)
RETURNS VARCHAR(64)
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @Result VARCHAR(64);
  
   DECLARE @InputMultiplier DECIMAL(38,0);
   DECLARE @OutputDivisor DECIMAL(38,0);
   DECLARE @OutputSuffix VARCHAR(11);

SELECT
   @InputMultiplier = 
      CASE @InputUOM
         WHEN 'Bytes'         THEN 1
         WHEN 'Byte'          THEN 1
         WHEN 'B'             THEN 1
         WHEN 'Kilobytes'     THEN 1024
         WHEN 'Kilobyte'      THEN 1024
         WHEN 'KB'            THEN 1024
         WHEN 'Megabytes'     THEN 1048576
         WHEN 'Megabyte'      THEN 1048576
         WHEN 'MB'            THEN 1048576
         WHEN 'Gigabytes'     THEN 1073741824
         WHEN 'Gigabyte'      THEN 1073741824
         WHEN 'GB'            THEN 1073741824
         WHEN 'Terabytes'     THEN 1099511627776
         WHEN 'Terabyte'      THEN 1099511627776
         WHEN 'TB'            THEN 1099511627776
         WHEN 'Petabytes'     THEN 1125899906842624
         WHEN 'Petabyte'      THEN 1125899906842624
         WHEN 'PB'            THEN 1125899906842624
         WHEN 'Exabytes'      THEN 1152921504606846976
         WHEN 'Exabyte'       THEN 1152921504606846976
         WHEN 'EB'            THEN 1152921504606846976
         WHEN 'Zettabytes'    THEN 1180591620717411303424
         WHEN 'Zettabyte'     THEN 1180591620717411303424
         WHEN 'ZB'            THEN 1180591620717411303424
         WHEN 'Yottabytes'    THEN 1208925819614629174706176
         WHEN 'Yottabyte'     THEN 1208925819614629174706176
         WHEN 'YB'            THEN 1208925819614629174706176
         WHEN 'Brontobytes'   THEN 1237940039285380274899124224
         WHEN 'Brontobyte'    THEN 1237940039285380274899124224
         WHEN 'BB'            THEN 1237940039285380274899124224
         WHEN 'Geopbytes'     THEN 1267650600228229401496703205376
         WHEN 'Geopbyte'      THEN 1267650600228229401496703205376
      END,
   @OutputDivisor =
      CASE @OutputUOM
         WHEN 'Bytes'         THEN 1
         WHEN 'Byte'          THEN 1
         WHEN 'B'             THEN 1
         WHEN 'Kilobytes'     THEN 1024
         WHEN 'Kilobyte'      THEN 1024
         WHEN 'KB'            THEN 1024
         WHEN 'Megabytes'     THEN 1048576
         WHEN 'Megabyte'      THEN 1048576
         WHEN 'MB'            THEN 1048576
         WHEN 'Gigabytes'     THEN 1073741824
         WHEN 'Gigabyte'      THEN 1073741824
         WHEN 'GB'            THEN 1073741824
         WHEN 'Terabytes'     THEN 1099511627776
         WHEN 'Terabyte'      THEN 1099511627776
         WHEN 'TB'            THEN 1099511627776
         WHEN 'Petabytes'     THEN 1125899906842624
         WHEN 'Petabyte'      THEN 1125899906842624
         WHEN 'PB'            THEN 1125899906842624
         WHEN 'Exabytes'      THEN 1152921504606846976
         WHEN 'Exabyte'       THEN 1152921504606846976
         WHEN 'EB'            THEN 1152921504606846976
         WHEN 'Zettabytes'    THEN 1180591620717411303424
         WHEN 'Zettabyte'     THEN 1180591620717411303424
         WHEN 'ZB'            THEN 1180591620717411303424
         WHEN 'Yottabytes'    THEN 1208925819614629174706176
         WHEN 'Yottabyte'     THEN 1208925819614629174706176
         WHEN 'YB'            THEN 1208925819614629174706176
         WHEN 'Brontobytes'   THEN 1237940039285380274899124224
         WHEN 'Brontobyte'    THEN 1237940039285380274899124224
         WHEN 'BB'            THEN 1237940039285380274899124224
         WHEN 'Geopbytes'     THEN 1267650600228229401496703205376
         WHEN 'Geopbyte'      THEN 1267650600228229401496703205376
      END,
   @OutputSuffix =
      CASE @OutputUOM
         WHEN 'Bytes'         THEN ' Bytes'     
         WHEN 'Byte'          THEN ' Bytes'
         WHEN 'B'             THEN ' Bytes'
         WHEN 'Kilobytes'     THEN ' KB'
         WHEN 'Kilobyte'      THEN ' KB'
         WHEN 'KB'            THEN ' KB'
         WHEN 'Megabytes'     THEN ' MB'
         WHEN 'Megabyte'      THEN ' MB'
         WHEN 'MB'            THEN ' MB'
         WHEN 'Gigabytes'     THEN ' GB'
         WHEN 'Gigabyte'      THEN ' GB'
         WHEN 'GB'            THEN ' GB'
         WHEN 'Terabytes'     THEN ' TB'
         WHEN 'Terabyte'      THEN ' TB'
         WHEN 'TB'            THEN ' TB'
         WHEN 'Petabytes'     THEN ' PB'
         WHEN 'Petabyte'      THEN ' PB'
         WHEN 'PB'            THEN ' PB'
         WHEN 'Exabytes'      THEN ' EB'
         WHEN 'Exabyte'       THEN ' EB'
         WHEN 'EB'            THEN ' EB'
         WHEN 'Zettabytes'    THEN ' ZB'
         WHEN 'Zettabyte'     THEN ' ZB'
         WHEN 'ZB'            THEN ' ZB'
         WHEN 'Yottabytes'    THEN ' YB'
         WHEN 'Yottabyte'     THEN ' YB'
         WHEN 'YB'            THEN ' YB'
         WHEN 'Brontobytes'   THEN ' BB'
         WHEN 'Brontobyte'    THEN ' BB'
         WHEN 'BB'            THEN ' BB'
         WHEN 'Geopbytes'     THEN ' GeopBytes'
         WHEN 'Geopbyte'      THEN ' GeopBytes'
      END
     
   SELECT @Result = CAST((@InputNumber * @InputMultiplier)/@OutputDivisor AS VARCHAR(49)) + @OutputSuffix;
  
   RETURN @Result;
END;

-- GRANT RIGHTS TO SMS/ConfigMgr REPORTS TO EXECUTE THE FUNCTION
GRANT EXECUTE ON dbo.udf_ConvertBytes TO smsschm_users, webreport_approle;
GO


--AND TEST IT TO MAKE SURE IT WORKS (CONVERT THE DISK SIZE MB TO GB)
SELECT
   ResourceID,
   dbo.udf_ConvertBytes(Size0,'MB','GB') AS Size
FROM
   dbo.v_GS_Logical_Disk
WHERE
   Name0 = 'C:'

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
Add to Google 

Published Tuesday, July 07, 2009 9:34 AM by jnelson

Comments

# re: UPDATED: SQL Function - udf_ConvertBytes - Converts any Byte-based unit of measure to any other Byte-based unit of measure (Bytes, MB, GB, TB, PB, EB, ZB, YB, BB, GeopBytes)

Tuesday, July 07, 2009 1:39 PM by gjones

you making "Brontobytes" up!! It is a host company! or a Fred Flinstone comment!

# re: UPDATED: SQL Function - udf_ConvertBytes - Converts any Byte-based unit of measure to any other Byte-based unit of measure (Bytes, MB, GB, TB, PB, EB, ZB, YB, BB, GeopBytes)

Tuesday, July 07, 2009 2:12 PM by jnelson

I know, right?  Sounds totally made up.  But I've seen it enough to add it to the script.  Not that anyone's going to really be using it anytime soon.

http://www.whatsabyte.com/

Powered by Community Server (Commercial Edition), by Telligent Systems