LEN vs DATALENGTH: Trailing Spaces, Unicode Bytes and NULL

Two length functions in SQL Server answer different questions. In LEN vs DATALENGTH, LEN counts characters but ignores trailing spaces, while DATALENGTH counts stored bytes. That difference can break validation, underestimate column width, and surprise an NVARCHAR or UTF-8 migration. Test both on the values that actually exist.

See LEN vs DATALENGTH Split on Trailing Spaces

For a varchar value with two trailing spaces, LEN omits the spaces while DATALENGTH includes their bytes. Both return NULL for a NULL input. This is not a defect in either function; it is a mismatch between the question asked and the function used.

DECLARE @v varchar(20) = 'ABC  ';
SELECT LEN(@v) AS len_result,
       DATALENGTH(@v) AS byte_result,
       LEN(CAST(@v AS varchar(max)) + '.') - 1
         AS characters_including_trailing_spaces,
       LEN(CAST(NULL AS varchar(20))) AS null_len,
       DATALENGTH(CAST(NULL AS varchar(20))) AS null_bytes;

The first two values are 3 and 5. Appending a sentinel character before LEN makes the spaces internal to the expression, then subtracting one gives 5 characters. I use that technique only when trailing spaces are meaningful and the source type is preserved through the expression. What does the application consider a valid value: ABC or ABC ?

Compare LEN vs DATALENGTH on VARCHAR and NVARCHAR

NVARCHAR stores UTF-16 code units, commonly two bytes for a basic character. VARCHAR uses a collation-dependent encoding. DATALENGTH measures storage bytes, not a universal character count. The same visible string can therefore have different byte lengths in varchar and nvarchar.

DECLARE @plain varchar(20) = 'ABC';
DECLARE @wide nvarchar(20) = N'ABC';
SELECT LEN(@plain) AS varchar_chars,
       DATALENGTH(@plain) AS varchar_bytes,
       LEN(@wide) AS nvarchar_chars,
       DATALENGTH(@wide) AS nvarchar_bytes;

The basic example returns three characters for both, three bytes for varchar, and six for nvarchar. Supplementary Unicode characters can use surrogate pairs; under SC-aware collations LEN can count one character while DATALENGTH reports four bytes. Do not divide every nvarchar byte length by two and call the result user-perceived characters.

Test a UTF-8 Collation Explicitly

SQL Server 2019 and later support UTF-8 collations for varchar data. Under UTF-8, a non-ASCII character can use multiple bytes in varchar even though LEN counts one character. Declare an explicit UTF-8 collation for a small test, and choose a collation available on the target version.

CREATE TABLE #Utf8Test
(
    ValueText varchar(20) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);
INSERT #Utf8Test(ValueText) VALUES (N'café');
SELECT LEN(ValueText) AS characters,
       DATALENGTH(ValueText) AS stored_bytes,
       ValueText
FROM #Utf8Test;
DROP TABLE #Utf8Test;

The accent character uses more than one UTF-8 byte. Check the result on the actual database collation and driver path. A migration from nvarchar to UTF-8 varchar can save bytes for mostly ASCII text while using multiple bytes for other scripts. Measure the real distribution instead of applying one universal factor.

Include MAX Columns and NULLs

varchar(max) and nvarchar(max) can hold values far beyond the 8,000-byte and 4,000-character declarations used for non-MAX columns. Both functions return bigint for MAX types and int for the others. A report that stores the result in int can overflow on very large values. NULL stays NULL unless you explicitly replace it.

DECLARE @large nvarchar(max) = REPLICATE(CAST(N'X' AS nvarchar(max)),5000);
SELECT LEN(@large) AS character_count,
       DATALENGTH(@large) AS byte_count;

This value has 5,000 basic characters and exactly 10,000 bytes of text data. Off-row storage and metadata add more physical pages than the value's DATALENGTH alone. The function measures the expression's bytes, not the total table footprint.

Inventory Every String Column Before Resizing

A proposed shrink from varchar(100) to varchar(50) needs evidence from existing rows. Query sys.columns for character types, then calculate maximum stored bytes and maximum characters including trailing spaces for each column. The dynamic script below targets one table and quotes identifiers. Run it on a restored copy first if the table is large, because every column scan costs I/O.

DECLARE @table sysname = N'CustomerNotes';
DECLARE @schema sysname = N'dbo';
DECLARE @column sysname, @sql nvarchar(max);
CREATE TABLE #ColumnLengths
(
    column_name sysname,
    max_bytes bigint NULL,
    max_characters bigint NULL
);
DECLARE cols CURSOR LOCAL FAST_FORWARD FOR
SELECT c.name
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(QUOTENAME(@schema)+N'.'+QUOTENAME(@table))
  AND t.name IN (N'char',N'varchar',N'nchar',N'nvarchar');
OPEN cols;
FETCH NEXT FROM cols INTO @column;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'INSERT #ColumnLengths
      SELECT @name, MAX(DATALENGTH('+QUOTENAME(@column)+N')),
             MAX(LEN(CONVERT(nvarchar(max),'+QUOTENAME(@column)+N')
                     + N''.'') - 1)
      FROM '+QUOTENAME(@schema)+N'.'+QUOTENAME(@table)+N';';
    EXEC sys.sp_executesql @sql,
         N'@name sysname', @name=@column;
    FETCH NEXT FROM cols INTO @column;
END;
CLOSE cols; DEALLOCATE cols;
SELECT * FROM #ColumnLengths ORDER BY column_name;

The character count is a practical measure for resizing and the byte count shows storage width. Inspect NULL counts and outliers separately. A char column reports its full declared width, because the padding is stored. For UTF-8 varchar, declared length is bytes, so max_bytes is the direct capacity check. For nvarchar(n), n counts byte-pairs, and supplementary characters need special attention. Do not change a column from the maximum alone without testing application writes and indexes.

Pick LEN vs DATALENGTH by the Rule

Use LEN when a rule concerns visible character length and trailing blanks are intentionally irrelevant. Use DATALENGTH when a byte limit or storage footprint matters. Use the sentinel method when trailing blanks are part of the data contract. In a migration, report LEN vs DATALENGTH and the sentinel measure, plus the current collation.

I verify a few longest rows by key rather than relying only on one aggregate. A maximum tells you the boundary but not whether an outlier is valid data, padding, or an encoding mistake. A safe resize preserves the meaning of the longest real value and the capacity required for future values.

Distinguish Display Length From Storage Limit

A user interface can limit “characters” while SQL Server column declarations limit bytes or UTF-16 code units depending on type. A varchar(50) under UTF-8 can store fewer than fifty multibyte characters. An nvarchar(50) can store fifty basic characters but fewer supplementary characters if each uses a surrogate pair. Validate the application's promised limit against the database representation and the actual collation.

Review Padding and Comparison Semantics

SQL Server string comparisons can treat trailing spaces differently from byte equality. Two values that compare equal can have different DATALENGTH results. If a code's trailing blanks are significant to an external system, normalize or preserve them explicitly and test round trips through the driver. If they are accidental padding, trim at an approved input boundary rather than silently shrinking a column based on LEN alone.

The sentinel technique counts those blanks, but it also assumes the appended period fits in the expression. Casting to a MAX type before appending avoids truncation at a fixed declared length. I include empty strings and NULL in the test set because both can appear in a real migration.

Inspect the Longest Rows

The dynamic aggregate gives a maximum per column. Follow it with TOP rows ordered by DATALENGTH for columns near the proposed limit. Inspect whether one outlier is valid, a data error, or a bulk import artifact. Also check indexes and computed columns that depend on the string width. A narrower definition can reduce row size, but a failed ALTER or truncated application input is a worse outcome than unused capacity.

Related reading on this blog: What is N in nvarchar? Interview Question of the Week #301 and ANSI PADDING and Storage: SQL in Sixty Seconds 210.

LEN is not byte length, it is a character count with trailing-space behavior to test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.