By default, bcp precedes each field that has a variable storage
length with a string of one or more bytes indicating the length of the field, which enables
the most compact file storage.
The default values in the prompts indicate the most efficient prefix length. For:
- Fixed-length fields – the prefix length should be 0.
- Fields of 255 bytes or less – the default prefix length is 1.
- text or image datatypes – the default prefix length
is 4
- binary and varbinary datatypes being converted to
char storage types – the default prefix length is 2, since each byte
of table data requires 2 bytes of file storage.
- binary, varbinary, and image data –
use even numbers for the prefix and length. This maintains consistency with Adaptive
Server, which stores data as an even number of hexadecimal digits.
- Any data column that permits null values – use a prefix length, other than 0, or a
terminator to denote the length of each row’s data. bcp considers
such columns, including columns with integer datatypes that might ordinarily be
considered fixed-length columns, to be of variable length.
- Data with no prefix before its column – use a prefix length of 0.
A prefix length is a 1-, 2-, or 4-byte integer that represents the length of each data
value in bytes, and it immediately precedes the data value in the host file.
Unless you supply a terminator, bcp pads each stored field with spaces
to the full length specified at the next prompt, “length.”
Because prefix lengths consist of native format integers, the resulting host file
contains nonprintable characters, which could prevent you from printing the host file or
from transmitting it through a communications program that cannot handle non-human-readable
characters.