The disable varbinary truncation configuration parameter enables or disables trailing zeros from varbinary and binary null data (see Chapter 5, “Setting Configuration Parameters” in the System Administration Guide, Volume 1).
By default, disable varbinary truncation is off for the server.
When Adaptive Server is set to truncate trailing zeros, tables you subsequently create store the varbinary data after truncating the trailing zeros. For example, if you create the test1 table with disable varbinary truncation set to 0:
create table test1(col1 varbinary(5))
Then insert some varbinary data with trailing zeros:
insert into test1 values(0x12345600)
Adaptive Server truncates the zeros:
select * from test1
col1 ------------ 0x123456
However, if you drop and re-create table test1 and set disable varbinary truncation to 1 (on) and perform the same steps, Adaptive Server does not truncate the zeros:
select * from test1
col1 ------------ 0x12345600
Adaptive Server considers data with or without trailing zeros as equal for comparisons (that is, 0x1234 is the same as 0x123400).
Because Adaptive Server stores data according to how disable varbinary truncation is currently set, tables may have a mix of data with or without trailing zeros, although the datatype does not change:
If you perform a select into to copy data from one table to another, Adaptive Server copies the data as it is stored (that is, if disable varbinary truncation is turned off, the trailing zeros are truncated). For example, using the tables from the examples above, if you disable varbinary truncation, then select data from table test1 into table test2:
sp_configure "disable varbinary truncation", 1 select * into test2 from test1
Then reinsert the same data again:
insert into test2 select * from test1
Table test2 does not truncate the trailing zeros because you ran select into with disable varbinary truncation set to 1, and the target table does not inherit the property from the source table. The data in the target table is truncated or preserved, depending on how the configuration parameters were setn when you ran select into:
select * from test2
col1 ------------ 0x12345600 0x12345600
Bulk copy (bcp) inserts data according to how disable varbinary truncation is set on the column when created.
You cannot use alter table to change the truncation behavior for a specific column. However, columns you add with alter table either truncate or preserve trailing zeros according to the value of disable varbinary truncation.
For example, if you create table test3 and column c1 with truncate trailing zeros disabled:
sp_configure "disable varbinary truncation", 1 create table test3(c1 varbinary(5)) insert into test3 values(0x123400)
c1 retains the trailing zeros:
select * from test3 c1 ---------- 0x123400
However, if you enable truncated trailing zeros and use alter table to add a new column, c2:
sp_configure "disable varbinary truncation", 0 alter table test3 add c2 varbinary(5) null insert into test3 values(0x123400, 0x123400)
c2 truncates the trailing zeros:
select * from test3 c1 c2 ---------- ---------- 0x123400 NULL 0x123400 0x1234
Trailing zeros are preserved in:
Worktables (after disable varbinary truncation is set to 1). The first example below includes a worktable in which the trailing zeros are retained, but in the second example, the worktable stores only the first 6 digits:
select 0x12345600 union select 0x123456 ------------- 0x12345600 select 0x123456 union select 0x12345600 ------------- 0x123456
Concatenations. For example:
select 0x12345600 + col1, col1 from test1
col1 -------------------- ------------ 0x123456001234560000 0x1234560000 0x1234560001234560 0x01234560 0x1234560012345600 0x12345600 0x123456000123456700 0x0123456700
Functions. For example:
select bintostr(0x12340000)
------------ 1234000
order by and group by queries. For example:
select col1 from (select 0x123456 col1 union all select 0x12345600 col1) temp1 order by col1
col1 ---------- 0x123456 0x12345600
If a query includes worktables, you must enable the disable
varbinary truncation configuration parameter before running
the query to ensure Adaptive Server performs no truncation.
Subqueries – trailing zeros are preserved unless the query involves a worktable, in which case the truncation depends on the value of disable varbinary truncation.
Dumps and loads – if the table data you dump includes trailing zeros, the trailing zeros are preserved when you load that data, regardless of the value of disable varbinary truncation in the target database.
Unions (see example for worktables, above).
convert. For example:
select convert(binary(5), 0x0000001000) ------------ 0x0000001000
