Differences Between LEN and DATALENGTH Functions in SQL Server

LEN() Built-in Function returns the number of characters in the string excluding trailing blanks.
DATALENGTH() Built-in Function returns the number of bytes used to represent the expression.

LEN() function does not take into account the trailing blanks:

DECLARE @test_var nvarchar(50)
SET @test_var = 'Test '
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN'

len_trailing_blanks

DATALENGTH() function does take into account the trailing blanks:

DECLARE @test_var varchar(50)
SET @test_var = 'Test '
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

datalength_trailing_blanks

varchar uses 1 byte to store one character:

DECLARE @test_var varchar(50)
SET @test_var = 'Test'
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_varchar

nvarchar uses 2 bytes to store one character:

DECLARE @test_var nvarchar(50)
SET @test_var = 'Test'
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_nvarchar

For char strings:

DECLARE @test_var char(50)
SET @test_var = 'Test '
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_char

For nchar strings:

DECLARE @test_var nchar(50)
SET @test_var = 'Test '
SELECT @test_var AS Text, LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_nchar

For int datatype variable:

DECLARE @test_var int
SET @test_var = '123456'
SELECT @test_var AS 'Var', LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_int

For Datetime variable:

DECLARE @test_var datetime
SET @test_var = GETDATE()
SELECT @test_var AS 'Var', LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_datetime

For decimal variable:

DECLARE @test_var decimal (18,8)
SET @test_var = 12345678.12345678
SELECT @test_var AS 'Var', LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_decimal

LEN and DATALENGHT of NULL is NULL:

DECLARE @test_var nvarchar(50)
SET @test_var = NULL
SELECT @test_var AS 'Var', LEN(@test_var) AS 'LEN', DATALENGTH(@test_var) AS 'DATALENGTH'

len_datalength_null