Field length

In almost all cases, use the bcp default value for the storage length while copying data out.

Note: The terms “length” and “storage length” in this discussion refer to the operating system file, not to Adaptive Server field lengths.

If you are creating:

A file to reload into Adaptive Server – the default prefixes and length keep the storage space needed to a minimum.
A human-readable file – the default length prevents the truncation of data or the creation of overflow errors that cause bcp to fail.

Be familiar with the data to transfer, since you can change the default length by supplying another value. If you are copying character data in from other software, examine the source file carefully before choosing length values.

Note: If the storage type is noncharacter, bcp stores the data in the operating system’s native data representation and does not prompt for a length.

When bcp converts noncharacter data to character storage, it suggests a default field length large enough to store the data without truncating datetime data or causing an overflow of numeric data.

The default lengths are the number of bytes needed to display the longest value for the Adaptive Server datatype.

Default Field Lengths for Noncharacter to Character Datatypes
Datatype	Default Size
`int`	12 bytes
`smallint`	6 bytes
`tinyint`	3 bytes
`float`	25 bytes
`money`	24 bytes
`bit`	1 byte
`datetime`	26 bytes
`smalldatetime`	26 bytes
`real`	25 bytes
`smallmoney`	24 bytes

If you specify a field length that is too short for numeric data when copying data out, bcp prints an overflow message and does not copy the data.
The default length for binary and varbinary fields is twice the length defined for the column, since each byte of the field requires 2 bytes of file storage.
If you accept the default storage length, the actual amount of storage space allocated depends on whether you specify a prefix length and terminators. If you specify a prefix length of:
- 1, 2, or 4 – bcp uses a storage space of the actual length of the data, plus the length of the prefix, plus any terminators.
- 0 and no terminator – bcp allocates the maximum amount of space shown in the prompt, which is the maximum space that may be needed for the datatype in question. bcp treats the field as if it were fixed length to determine where one field ends and the next begins.
  
  For example, if the field is defined as varchar(30), bcp uses 30 bytes for each value, even if some of the values are only 1 character long.
Fields defined in the database as char, nchar, and binary, and those that do not permit null values, are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary(8).
If data in the varchar and varbinary fields is longer than the length specified for copy out, bcp silently truncates the data in the file at the specified length.
bcp does not know how large any one data value will be before copying all the data, so it always pads char datatypes to their full specified length.
The file storage type and length of a column need not be the same as the type and length of the column in the database table. The coy fails if the types and formats copied in are incompatible with the structure of the database table.
File storage length generally indicates the maximum amount of transferable data for the column, excluding terminators and/or prefixes.
When copying data into a table, bcp observes any defaults defined for columns and user-defined datatypes, but ignores rules in order to load data at the fastest possible speed.
bcp considers any data column that can contain a null value to be variable length, so use either a length prefix or a terminator to denote the length of each row of data.
The file storage type and length of a column need not be the same as the type and length of the column in the database table. (If types and formats copied in are incompatible with the structure of the database table, the copy fails.)