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. This prefix 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.
For fields of 255 bytes or less, the default prefix length is 1.
For text or image datatypes, the default prefix length is 4.
For binary and varbinary datatypes that are being converted to char storage types, the default prefix length is 2, since each byte of table data requires 2 bytes of file storage.
For binary, varbinary, and image data, use even numbers for the prefix and length. This requirement maintains consistency with Adaptive Server, which stores data as an even number of hexadecimal digits.
For 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.
For 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. 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. The nature of these characters could prevent you from printing the host file or from transmitting it through a communications program that cannot handle non-human-readable characters.
For more information about prefix lengths, see Table 3-9.
