Prefix Length

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 the SAP ASE 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.