Interview Question - What is the difference between LEN() and DATALENGTH() function ?

This question was faced by friend in a project allocation interview.

From the look of it both the function appears to be same but they are not. One gives the total number  (count) of characters in the string while second gives the number of bytes occupied by the characters in the string. 

As a SQL developer while dealing with string, quite often we end up using LEN() function. But DATALENGTH() isn't used that often.

The LEN() function is a String function while DATLENGTH() comes under data type function.

To understand the output of DATALENGTH() function one must know how much bytes are occupied for the different datatypes. 

As you can see the screenshot below 

LEN() function for INT variable with value 8 returns 1 while DATALEGTH() returns 4. This is because INT datatype takes up 4 bytes in the memory.

Similarly for CHAR data type is accounted for 1 byte hence both the function are returning 1 as a output. While NCHAR() occupies 2 bytes/character hence LEN() returns 1 while DATALENGTH() returns 2 for single character.

DECLARE @I INT = 8,
  @Str CHAR(1) = 'c',
  @Str1 NCHAR(1) = 'c'
SELECT LEN(@I) AS Len_Function,DATALENGTH(@I) AS DataLength_Function  

SELECT LEN(@Str) AS Len_Function,DATALENGTH(@Str) AS DataLength_Function  

SELECT LEN(@Str1) AS Len_Function,DATALENGTH(@Str1) AS DataLength_Function  

For more details on the function please refer following links

https://msdn.microsoft.com/en-us/library/ms190329.aspx

https://msdn.microsoft.com/en-us/library/ms173486.aspx