SQL Server DATALENGTH Function


By:

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 query results

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 query 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.

datalength query results

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]
datalength query results

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.

datalength query results

Related Articles


Last Update: 11/2/2021




Comments For This Article





download














vote


get free sql tips
agree to terms