LEN vs DATALENGTH

What are the differences between LEN() and DATALENGTH()

[LEN() vs DATALENGTH()]?

	LEN()	DATALENGTH()
Use	LEN() is used to return the number of characters in a String.	DATALENGTH() is used to return the number of bytes used by any expression/column. DATALENGTH() is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
Behaviors	Input parameter is string expression and LEN would allow the input parameter that could be constant OR variable OR column of character OR binary data OR NULL. NOTE: It does not allow the TEXT and IMAGE data type as input parameter.	Input parameter is expression and it could be any data type.
	LEN() function does NOT include trailing spaces of string expression as part of the LEN() measurement.	DATALENGTH() is used to return the number of bytes used by any expression/column which data type is NVARCHAR (UNICODE character string) , in this case two bytes are required for every  character. It means  DATALENGTH of UNICODE character string will be equal to the number characters including spaces multiplied by 2.
Return Data Type	BIGINT if expression is of the varchar(max), nvarchar(max) OR varbinary(max) data types; otherwise, INT.	BIGINT if expression is of the varchar(max), nvarchar(max) OR varbinary(max) data types; otherwise, INT.
Syntax	LEN(String_Expression)	DATALENGTH(String_Expression)
Example with Different Data Types
NULL	SELECT LEN(NULL) AS 'LEN' RESULT:  NULL	SELECT DATALENGTH(NULL) AS 'DATALENGTH' RESULT:  NULL
Char	DECLARE @Char CHAR(100) = '12345678' PRINT LEN(@Char) RESULT:  8	DECLARE @Char CHAR(100) = '12345678' PRINT DATALENGTH(@Char) RESULT:  100 CHAR used to store fixed length character value as String.
Char With Space	DECLARE @CharSpace CHAR(100) = '12345678  ' PRINT LEN(@CharSpace) RESULT:  8	DECLARE @CharSpace CHAR(100) = '12345678  ' PRINT DATALENGTH(@CharSpace) RESULT:  100 CHAR used to store fixed length character value as String.
NChar	DECLARE @NChar NCHAR(100) = '12345678' PRINT LEN(@NChar) RESULT:  8	DECLARE @NChar CHAR(100) = '12345678' PRINT DATALENGTH(@NChar) RESULT:  100 CHAR used to store fixed length character value as String.
NChar With Space	DECLARE @NCharSpace NCHAR(100) = '12345678  ' PRINT LEN(@NCharSpace) RESULT:  8	DECLARE @NCharSpace NCHAR(100) = '12345678  ' PRINT DATALENGTH(@NCharSpace) RESULT:  200 CHAR used to store fixed length character value as String. It consumes 2 bytes per character for supporting to store Unicode characters.
VarChar	DECLARE @VarChar VARCHAR(100) = '12345678' PRINT LEN(@VarChar) RESULT:  8	DECLARE @VarChar VARCHAR(100) = '12345678' PRINT DATALENGTH(@VarChar) RESULT:  8
VarChar With Space	DECLARE @VarCharSpace VARCHAR(100) =  '12345678     ' PRINT LEN(@VarCharSpace) RESULT:  8	DECLARE @VarCharSpace VARCHAR(100) =  '12345678     ' PRINT DATALENGTH(@VarCharSpace) RESULT:  13 Here, Spaces also considered as used bytes for storing into NVARCHAR data type.
NVarChar	DECLARE @NVarChar NVARCHAR(100) =  '12345678' PRINT LEN(@NVarChar) RESULT:  8	DECLARE @NVarChar NVARCHAR(100) =  '12345678' PRINT DATALENGTH(@NVarChar) RESULT:  16 Here, Two bytes are required for every character for storing into NVARCHAR data type.
NVarChar With Space	DECLARE @NVarCharSpace NVARCHAR(100) =  '12345678     ' PRINT LEN(@NVarCharSpace) RESULT:  8	DECLARE @NVarCharSpace NVARCHAR(100) =  '12345678     ' PRINT DATALENGTH (@NVarCharSpace) RESULT:  26 Here, Two bytes are required for every character for storing into NVARCHAR data type.
Text	DECLARE @Employee TABLE (EmployeeText TEXT) INSERT INTO @Employee VALUES('This is text') SELECT LEN(EmployeeText) AS 'LEN' FROM @Employee RESULT:  Argument data type text is invalid for argument 1 of len function.	DECLARE @Employee TABLE (EmployeeText TEXT) INSERT INTO @Employee VALUES('This is text') SELECT DATALENGTH(EmployeeText) AS 'DATALENGTH' FROM @Employee RESULT:  12
Image	DECLARE @Employee TABLE (EmployeeImage IMAGE) INSERT INTO @Employee VALUES('This is image') SELECT LEN(EmployeeImage) AS 'LEN' FROM @Employee RESULT:  Argument data type image is invalid for argument 1 of len function.	DECLARE @Employee TABLE (EmployeeImage IMAGE) INSERT INTO @Employee VALUES('This is image') SELECT DATALENGTH(EmployeeImage) AS 'DATALENGTH' FROM @Employee RESULT:  13

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF