67

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.

skia.heliou
  • 1,670
  • 2
  • 19
  • 30
Tewr
  • 3,573
  • 1
  • 29
  • 43

3 Answers3

124

Actually, you can do this in T-SQL!

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

James A Mohler
  • 10,884
  • 14
  • 43
  • 67
mwigdahl
  • 15,640
  • 7
  • 51
  • 63
31

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
bkaid
  • 50,475
  • 21
  • 110
  • 127
John Sansom
  • 40,385
  • 9
  • 71
  • 82
0
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
Meysam Ghorbani
  • 45
  • 4