Recently I faced a issue when using len() in a query to find out the length of a query, len() was not counting the trailing spaces in the value. But datalength() is counting the trailing spaces also.
Does this means that if I'm doing some operation which deals with the actual length of the value then I have to use dalalength() over len().
ex: If I need the value of a particular value to be is of 10 character length. i.e. If the value is 3 character length I've to append 7 spaces to it.
Yes that's exactly what you must do. If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH().
Even LEN() documentation has an information that to get number of bytes to represent the extension you should use DATALENGTH()
Here are the links to MSDN docs: