varbinarydata types store strings of bits. Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern, e.g. 0x2B1D. Both LEN and DATALENGTH can be used with varbinary datatype. However, they have some difference. If not used properly, you may not get valid results.
LEN returns 3 and DATALENGTH returns 4.
This is becuase
LEN converts the varinary to string first and then count the number of charater.
Run the following query
CHAR(0x42), CHAR(0x56), CHAR(0x2A), CHAR(0x20)
get the string that it represents is BV* (4 characters with the forth one is a trailing space).
LEN ignores all trailling space, it returns 3. However, LEN does not ignore the trailing TAB, LF, and CR.
.g. for TAB (0x09):
So for the length of varbinary data type, it’s safe always to use DATALENGTH.