How do you really create a zero-length binary value in SQL Server?

Question

I'm writing an ODBC binding and I decided to implement support for binary and I've found that if I insert 0x, supposedly the "empty string" MSDN docs, it returns a buffer of one byte with zero in it. Clearly, that does not have length 0:

1> CREATE TABLE demo (bar BINARY)
2> GO
1> INSERT INTO demo VALUES (0x)
2> GO
(1 rows affected)
1> SELECT * FROM demo WHERE bar = 0x00
2> GO
bar 
----
0x00

(1 rows affected)
1> SELECT DATALENGTH(bar) FROM demo WHERE bar = 0x00
2> go

-----------
          1

However, if you select it directly, it is empty:

1> SELECT DATALENGTH(0x)
2> go

-----------
          0

(1 rows affected)

So it seems like pulling something from the table has some kind of "minimum" of one zero byte?

The problem I have is that we don't have a very basic put/get law "what you put in is what you get out". If someone uses my ODBC binding and inserts empty data, they will not get empty data back. This is broken.

What am I missing?

Lukasz Szozda · Accepted Answer · 2018-03-21 17:08:05Z

4

The point is simple BINARY is like CHAR(1). You need to use VARBINARY.

From binary and varbinary

binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

and

varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

CREATE TABLE demo (bar VARBINARY(size));
INSERT INTO demo VALUES (0x);
SELECT DATALENGTH(bar), * FROM demo;

--length    bar
-- 0        0x

DBFiddle Demo

answered Mar 21, 2018 at 17:08

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do you really create a zero-length binary value in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related