Tuesday, September 30, 2008

Why LEN differs from DATALENGTH when using BINARY data

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.

No comments: