Skip to content

datalength-transact-sql.md

Latest commit

 

History

History
82 lines (67 loc) · 3.23 KB

datalength-transact-sql.md

File metadata and controls

82 lines (67 loc) · 3.23 KB
title DATALENGTH (Transact-SQL)
description DATALENGTH (Transact-SQL)
author rwestMSFT
ms.author randolphwest
ms.date 08/20/2019
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
DATALENGTH_TSQL
DATALENGTH
helpviewer_keywords
number of bytes representing expression
data types [SQL Server], length
DATALENGTH function
expressions [SQL Server], length
lengths [SQL Server], data
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

DATALENGTH (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]

This function returns the number of bytes used to represent any expression.

Note

To return the number of characters in a string expression, use the LEN function.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DATALENGTH ( expression )

Arguments

expression
An expression of any data type.

Return types

bigint if expression has an nvarchar(max), varbinary(max), or varchar(max) data type; otherwise int.

Remarks

DATALENGTH becomes really helpful when used with data types that can store variable-length data, such as:

  • image
  • ntext
  • nvarchar
  • text
  • varbinary
  • varchar

For a NULL value, DATALENGTH returns NULL.

Note

Compatibility levels can affect return values. See ALTER DATABASE Compatibility Level (Transact-SQL) for more information about compatibility levels.

Note

Use the LEN to return the number of characters encoded into a given string expression, and DATALENGTH to return the size in bytes for a given string expression. These outputs may differ depending on the data type and type of encoding used in the column. For more information on storage differences between different encoding types, see Collation and Unicode Support.

Examples

This example finds the length of the Name column in the Product table:

USE AdventureWorks2022  
GO
SELECT length = DATALENGTH(EnglishProductName), EnglishProductName  
FROM dbo.DimProduct  
ORDER BY EnglishProductName;  
GO

See also

LEN (Transact-SQL)
CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
System Functions (Transact-SQL)