SQL Server DataLength issues

Question

I have a column with the following data: SMITH SMITH SMITH SMITH SMITH. When doing a SELECT DATALENGTH from the table, I am getting a value of 72.

However when I copy the column data and pop it in the select statement like the below, I get a length of 36.

SELECT DATALENGTH('JUDITH KOSGEY JUDITH JEPKORIR KOSGEY')

What could be the possible reasoning for this? I do no see any extra spaces or special characters in this text.

Answer 1

This could be because the data column is UNICODE which takes more storage per character. What's the type of the column?

Answer 2

If your column is NVARCHAR, it is intended to handle double byte character sets.

Use LEN(<column>) instead of DATALENGTH(<column>) and you will get the value you are expecting.