SQL DATALENGTH Function Use and Examples

Overview

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.

Explanation

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.

Additional Information

• SQL LEN

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018