The differences between LEN and DATALENGTH in SQL Server!
I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion.
First let’s take a look what Books On Line has to say about these two functions
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Returns the number of bytes used to represent any expression.
So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the charaters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.
Take a look at this example
declare @v nchar(5) select @v ='ABC ' select len(@v),datalength(@v)
The output for len is 3 while the output for datalength =10. The reason that datalength returns the value 10 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per charaters
Let’s take a look at some more data, first create this table
create table #TeslLen ( CharCol char(5), VarCharCol varchar(5), NCharCol nchar(5), NVarCharCol nvarchar(5)) insert #TeslLen values('A','A','A','A') insert #TeslLen values('AB','AB','AB','AB') insert #TeslLen values('ABC','ABC','ABC','ABC') insert #TeslLen values('ABCD','ABCD','ABCD','ABCD') insert #TeslLen values('ABCDE','ABCDE','ABCDE','ABCDE') insert #TeslLen values(' ',' ',' ',' ')
Now run the following query
select CharCol as Value,len(CharCol) as LenChar,DATALENGTH(CharCol) as DLenChar, len(VarCharCol) as LenVarChar,DATALENGTH(VarCharCol)as DLenVarChar, len(NCharCol) as LenNChar,DATALENGTH(NCharCol) as DLenNChar, len(NVarCharCol) as LenNVarChar,DATALENGTH(NVarCharCol) as DLenNVarChar from #TeslLen
Here is the output for all the columns with LEN and DATALENGTH