SQL Server : GET Number of Bytes Using DATALENGTH() Function

We can get the length of a string with the LEN() function, but this function does not represent the number of bytes stored in the database. To find out how many bytes a data value is, we can use the LENGTH() function.

The DATALENGTH() function returns the number of bytes used to represent the expression.

I made an article about the difference between VARCHAR and NVARCHAR, and one of them is how these two data type store values in bytes. One VARCHAR character is 1 byte in size while 1 NVARCHAR character is 2 bytes.

DECLARE @String1 VARCHAR(50) = 'Rani Irsan'
DECLARE @String2 NVARCHAR(50) = 'Rani Irsan'

SELECT DATALENGTH(@String1) AS [VarcharByte], DATALENGTH(@String2) AS [NVarcharByte]

While the LEN() function counts only leading spaces, the DATALENGTH() function counts leading and trailing spaces when calculating the length of the expression.

DECLARE @String VARCHAR(50) = 'Rani Irsan   '
SELECT DATALENGTH(@String) AS [DataLength], LEN(@String) AS [Len]