SQL Server DATALENGTH Function

By: Daniel Calbimonte

The DATALENGTH function is used to show the number of bytes used to represent an expression. This is useful to see the space used for a column in a database table. The number returned by the function can be int or bigint.

Syntax

DATALENGTH(expression)

Parameters

• expression – String or expression, can be any data type.

Simple DATALENGTH Example

Below is a simple example using the AdventureWorks database with DATALENGTH. We will show the number of bytes used by a varbinary(max) column. We will measure the size of gif photos in bytes.

SELECT DATALENGTH([LargePhoto]) bytes,[LargePhotoFileName]
FROM [Production].[ProductPhoto]

The query will show the bytes used per image.

DATALENGTH in KBytes Example

The next example will show the KBytes used per image.

SELECT DATALENGTH([LargePhoto])/1024.00 Kbytes,[LargePhotoFileName]
FROM [Production].[ProductPhoto] 

Here are the results.

DATALENGTH in MB Example

This example, will show the column size in MB.

SELECT DATALENGTH([LargePhoto])/1048576.00 Mbytes,[LargePhotoFileName]
FROM [Production].[ProductPhoto]

The query will produce the following output.

Get SUM of DATALENGTH Example

The following shows how we can use the SUM function to get the overall space used per column using DATALENGTH.

SELECT SUM(DATALENGTH([LargePhoto])) as PhotoBytes, SUM(DATALENGTH([LargePhotoFileName])) as FilenameBytes
FROM [Production].[ProductPhoto]

Bytes Used Per Data Type

The following example will return the bytes used per data type. This shows how much space the same value takes to be stored depending on the type of data type that is used.

DECLARE @myint int = 1
DECLARE @mysmallint smallint = 1
DECLARE @mydecimal decimal = 1
DECLARE @mybigint bigint = 1
 
SELECT 'int' datatype, DATALENGTH(@myint) bytes
UNION
SELECT 'smallint' datatype, DATALENGTH(@mysmallint) bytes
UNION
SELECT 'decimal' datatype, DATALENGTH(@mydecimal) bytes
UNION
SELECT 'bigint' datatype, DATALENGTH(@mybigint) bytes

The output is the following.

Related Articles

• SQL LEN