Why LEN differs from DATALENGTH when using BINARY data

Tue Jul 22, 2008 by Peter Larsson in sql-server-2008, sql-server-2005, sql-server-2000

Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH.

Number	Binary digits	VARBINARY	LEN	DATALENGTH
32	00000000 00100000	0x00000020	3	4
288	00000001 00100000	0x00000120	3	4
544	00000010 00100000	0x00000220	3	4
800	00000011 00100000	0x00000320	3	4
1056	00000100 00100000	0x00000420	3	4
1312	00000101 00100000	0x00000520	3	4
1568	00000110 00100000	0x00000620	3	4
1824	00000111 00100000	0x00000720	3	4
2080	00001000 00100000	0x00000820	3	4
2336	00001001 00100000	0x00000920	3	4
2592	00001010 00100000	0x00000A20	3	4
2848	00001011 00100000	0x00000B20	3	4
3104	00001100 00100000	0x00000C20	3	4
3360	00001101 00100000	0x00000D20	3	4
3616	00001110 00100000	0x00000E20	3	4
3872	00001111 00100000	0x00000F20	3	4
4128	00010000 00100000	0x00001020	3	4
4384	00010001 00100000	0x00001120	3	4
4640	00010010 00100000	0x00001220	3	4
4896	00010011 00100000	0x00001320	3	4
5152	00010100 00100000	0x00001420	3	4
5408	00010101 00100000	0x00001520	3	4
5664	00010110 00100000	0x00001620	3	4
5920	00010111 00100000	0x00001720	3	4
6176	00011000 00100000	0x00001820	3	4
6432	00011001 00100000	0x00001920	3	4
6688	00011010 00100000	0x00001A20	3	4
6944	00011011 00100000	0x00001B20	3	4
7200	00011100 00100000	0x00001C20	3	4
7456	00011101 00100000	0x00001D20	3	4
7712	00011110 00100000	0x00001E20	3	4
7968	00011111 00100000	0x00001F20	3	4
8224	00100000 00100000	0x00002020	2	4
8480	00100001 00100000	0x00002120	3	4
8736	00100010 00100000	0x00002220	3	4
8992	00100011 00100000	0x00002320	3	4

First column is the INT value, the second column is the binary representation of the number and third column is the binary value of the number.
The fourth column is the LEN and fifth column is the DATALENGTH.

When you apply LEN to a binary value, SQL Server tries to convert the value to a string to calculate the length.
As you can see for all values above, they all end with binary 0x20!
And number 8224 even end with double 0x20 (0x2020).

Do you know what ascii character 0x20 is? It is the space character, ascii 32.

And since LEN function truncates trailing spaces, you get a smaller value for LEN than DATALENGTH which always counts all characters.

Legacy Comments


Kekerode 2008-07-25	re: Why LEN differs from DATALENGTH when using BINARY data Thank you for this very nice example.

phe 2008-07-28	re: Why LEN differs from DATALENGTH when using BINARY data I found the same issue before. Check http://p-sql.spaces.live.com/blog/cns!CADFA1CB6D2C0E32!343.entry