Bytes vs Characters: The SQL Server Showdown Between DATALENGTH and LEN

When working with strings in SQL Server, we often need to understand how much space they occupy and how many characters they contain. While most developers are familiar with the LEN() function, fewer explore the subtle but powerful DATALENGTH() function. This article explores the key difference between the two, and demonstrates their use with a simple yet insightful query comparing VARCHAR and NVARCHAR strings.

DATALENGTH() Decoded: When, Why & Where It Matters

The DATALENGTH() function returns the number of bytes used to store data. This is especially useful in scenarios like:

• Performance tuning: Identify columns consuming high storage.
• Auditing & size validation: Check how much space strings are taking.
• Understanding Unicode vs Non-Unicode storage.
• When transmitting data over networks or APIs, where byte size matters.

In contrast, LEN() simply counts the number of characters, ignoring trailing spaces, and doesn’t reflect the underlying byte-level footprint.

How It Differs from LEN() – With Example

--Comparing LEN & DATALENGHT of the string
DECLARE @Country VARCHAR(100) 
SET @Country= 'India';
DECLARE @Country_Unicode NVARCHAR(100) 
SET @Country_Unicode = 'India';

SELECT
    'VARCHAR' AS DataType,
    LEN(@Country) AS Length,
    DATALENGTH(@Country) AS BytesUsed
UNION ALL
SELECT
    'NVARCHAR',
    LEN(@Country_Unicode),
    DATALENGTH(@Country_Unicode);

This clearly shows that while both strings appear the same, their storage cost is different, which LEN() cannot reveal.