datalength-transact-sql.md

title	DATALENGTH (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	07/29/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology	t-sql
ms.topic	language-reference
f1_keywords	DATALENGTH_TSQL DATALENGTH
dev_langs	TSQL
helpviewer_keywords	number of bytes representing expression data types [SQL Server], length DATALENGTH function expressions [SQL Server], length lengths [SQL Server], data
ms.assetid	00f377f1-cc3e-4eac-be47-b3e3f80267c9
author	MashaMSFT
ms.author	mathoma
manager	craigg
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

DATALENGTH (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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

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

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

data types, because these data types can store variable-length data.

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.

Examples

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

-- Uses AdventureWorks  
  
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)