All binary not null columns are padded with zeros to the full width of the column. Trailing zeros are truncated in all varbinary data and in binary null columns, since columns that accept null values must be treated as variable-length columns.
The following example creates a table with all four variations of binary and varbinary datatypes, NULL and NOT NULL. The same data is inserted in all four columns and is padded or truncated according to the datatype of the column.
create table zeros (bnot binary(5) not null,
bnull binary(5) null,
vnot varbinary(5) not null,
vnull varbinary(5) null)
insert zeros values (0x12345000, 0x12345000, 0x12345000, 0x12345000)
insert zeros values (0x123, 0x123, 0x123, 0x123)
select * from zeros
bnot bnull vnot vnull ------------ --------- ---------- --------- 0x1234500000 0x123450 0x123450 0x123450 0x0123000000 0x0123 0x0123 0x0123
Because each byte of storage holds 2 binary digits, Adaptive Server expects binary entries to consist of the characters “0x” followed by an even number of digits. When the “0x” is followed by an odd number of digits, Adaptive Server assumes that you omitted the leading 0 and adds it for you.
Input values “0x00” and “0x0” are stored as “0x00” in variable-length binary columns (binary null, image and varbinary columns). In fixed-length binary (binary not null) columns, the value is padded with zeros to the full length of the field:
insert zeros values (0x0, 0x0,0x0, 0x0)
select * from zeros where bnot = 0x00
bnot bnull vnot vnull ---------- ------ ----- ------------ 0x0000000000 0x00 0x00 0x00
If the input value does not include the “0x”, Adaptive Server assumes that the value is an ASCII value and converts it. For example:
create table sample (col_a binary(8)) insert sample values (’002710000000ae1b’) select * from sample
col_a ------------------ 0x3030323731303030
