Field Length

In almost all cases, you should accept the bcp default value for the storage length. If you are making a file for later reloading into SQL Server, using a prefix with the default storage type and the default length keeps the storage space to a minimum. If you are creating an ASCII file, using the default length ensures that you will not truncate the data or create overflow errors that cause bcp to fail.

You can change the default length by supplying another value.

If the storage type is noncharacter, the data is stored in the operating system native data representation. You are not asked to provide 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 overflow of numeric data.

These are the default field lengths for noncharacter fields:

If you specify a field length that is too short for number data when you are copying data out, bcp prints an overflow message and does not copy the data. When datetime data is stored as a character string of less than 26 bytes, the data is silently truncated.

The default length for binary and varbinary fields is twice the length defined for the column, since 2 bytes of storage are required for each byte of the field. If you accept the default, the actual amount of storage space allocated depends on whether you specify a prefix length and/or terminators:

• If you specify a prefix length of 1, 2, or 4, the storage space used is the actual length of the data, plus the length of the prefix, plus any terminators.
• If you specify a prefix length of 0 and no terminator, bcp allocates the maximum amount of space shown in the prompt, which is the maximum space that might be needed for the datatype in question. In other words, the field is treated as if it were of fixed length so that it is possible to determine where one field ends and the next begins.

  For example, if the field is defined as varchar(30), bcp uses 30 characters for each value, even if some of the values are only 1 character long. The char datatypes are always padded to their full specified length.

The following tables show the interaction of prefix lengths, terminators, and field length on the information in the file. "P" indicates the prefix in the stored table, "T" indicates the terminator, and dashes (--) indicate appended spaces. An ellipsis (...) indicates that the pattern repeats for each field. The field length is 8 for each column, and the 6-character field "string" is stored each time.

SQL Server char Data

Other Datatypes Converted to char Storage