Size of VARBINARY field in SQL Server 2005

Question

I am trying to determine the size in bytes of the contents in a VARBINARY(MAX) field in SQL Server 2005, using SQL. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated.

Answer 1

Actually, you can do this in T-SQL!

DATALENGTH(<fieldname>) will work on varbinary(max) fields.

Answer 2

The VARBINARY(MAX) field allocates variable length data up to just under 2GB in size.

You can use DATALENGTH() function to determine the length of the column content.

For example:

SELECT DATALENGTH(CompanyName), CompanyName
FROM Customers

Answer 3

CREATE FUNCTION [dbo].[FileDataSizeUnit_FN] (@FileData VARBINARY(MAX))
RETURNS VARCHAR(3)
AS
    BEGIN 
        DECLARE @Unit VARCHAR(3),
            @ByteLen AS NUMERIC(16,2)

        SET @ByteLen = ISNULL(DATALENGTH(@FileData),0)

        SET @Unit = CASE WHEN @ByteLen < 1000 THEN 'B'
        WHEN @ByteLen < 100000 THEN 'KB'
        WHEN @ByteLen < 1000000000 THEN 'MB'
        ELSE 'GB' END
    
        RETURN  @Unit
    END
GO

CREATE FUNCTION [dbo].[FileDataSize_FN] (@FileData VARBINARY(MAX))
RETURNS NUMERIC(16,2)
AS
    BEGIN 
        DECLARE @Size AS NUMERIC(16,2),
            @ByteLen AS NUMERIC(16,2)

        SET @ByteLen = ISNULL(DATALENGTH(@FileData),0)

        SET @Size = CASE WHEN @ByteLen <1000 THEN @ByteLen
        WHEN @ByteLen < 100000 THEN @ByteLen/1024.0 
        WHEN @ByteLen < 1000000000 THEN @ByteLen/1024.0/1024 
        ELSE CAST(@ByteLen/1024.0/1024/1024 AS NUMERIC(16,2)) END
    
        RETURN  @Size
    END
GO

SELECT dbo.FileDataSize_FN(F.FileData) AS Size,
        dbo.FileDataSizeUnit_FN(F.FileData) AS SizeUnit
    FROM dbo.Files_Tbl F