DATALENGTH vs LEN function in SQL

DATALENGTH function returns the size used by a column or an expression whereas the LEN function returns the number of characters.

Let’s explore with few examples

The DATALENGTH function returns 4 bytes for an integer and LEN function returns the number of characters

DECLARE @Type INT
SET @Type=125
SELECT LEN(@Type) [Length], DATALENGTH(@Type) [DataLength]

Length      DataLength
----------- -----------
3           4

The DATALENGTH and LEN functions return 9 bytes and 9 characters respectively for VARCHAR. Though the size is declared as 10 space is allocated based on the value assigned.

DECLARE @Type VARCHAR(10)
SET @Type='intellect'
SELECT LEN(@Type) [Length], DATALENGTH(@Type) [DataLength]

Length      DataLength
----------- -----------
9           9

The DATALENGTH returns 18 bytes for the NVARCHAR type and the LEN function still returns the character count as 9.

DECLARE @Type NVARCHAR(10)
SET @Type=N'intellect'
SELECT LEN(@Type) [Length], DATALENGTH(@Type) [DataLength]

Length      DataLength
----------- -----------
9           18

The LEN function does not support the data type TEXT, as shown in the below example the statement returns the error message “Argument data type text is invalid for argument 1 of len function”

DECLARE @Type AS TABLE (Col TEXT)
INSERT INTO @Type VALUES('intellect')
SELECT *, LEN(Col) [Length] FROM @Type

The following query returns the size of the Text column using DATALENGTH function

DECLARE @Type AS TABLE (Col TEXT)
INSERT INTO @Type VALUES('platform to share ideas and knowledge about SQL Server')
SELECT DATALENGTH(Col) [DataLength] FROM @Type