Specify Field Length by Using bcp
Collapse the table of content
Expand the table of content

Specify Field Length by Using bcp (SQL Server)

 

Applies To: SQL Server 2016

The field length indicates the maximum number of characters that are required to represent data in character format. The field length is already known if the data is stored in the native format; for example, the int data type takes 4 bytes. If you have indicated 0 for the prefix length, the bcp command prompts you for field length, the default field lengths, and the impact of field-length on data storage in data files that contain char data.

If an interactive bcp command contains the in or out option without either the format file switch (-f) or a data-format switch (-n, -c, -w, or -N), the command prompts for the field length of each data field, as follows:

Enter length of field <field_name> [<default>]:

For an example that shows this prompt in context, see Specify Data Formats for Compatibility when Using bcp (SQL Server).

System_CAPS_ICON_note.jpg Note


After you interactively specify all of the fields in a bcp command, the command prompts you save your responses for each field in a non-XML format file. For more information on non-XML format files, see Non-XML Format Files (SQL Server).

Whether a bcp command prompts for field length depends on several factors, as follows:

  • When you copy data types that are not of fixed length and you specify a prefix length of 0, bcp prompts for a field length.

  • When converting noncharacter data to character data, bcp suggests a default field length large enough to store the data.

  • If the file storage type is noncharacter, the bcp command does not prompt for a field length. The data is stored in the Microsoft SQL Server native data representation (native format).

Generally, Microsoft recommends that you accept the bcp-suggested default values for the field length. When a character mode data file is created, using the default field length ensures that data is not truncated and that numeric overflow errors do not occur.

If you specify a field length that is incorrect, problems can occur. For instance, if you copy numeric data and you specify a field length that is too short for the data, the bcp utility prints an overflow message and does not copy the data. Also, if you export datetime data and specify a field length of less than 26 bytes for the character string, the bcp utility truncates the data without an error message.

System_CAPS_ICON_important.jpg Important


When the default size option is used, SQL Server expects to read an entire string. In some situations, use of a default field length can lead to an "unexpected end of file" error. Typically, this error occurs with the money and datetime data types when only part of the expected field occurs in the data file; for example, when a datetime value of mm/dd/yy is specified without the time component and is, therefore, shorter than the expected 24 character length of a datetime value in char format. To avoid this type of error, use field terminators or fixed-length data fields, or change the default field length by specifying another value.

Default Field Lengths for Character File Storage

The following table lists the default field lengths for data to be stored as a character-file storage type. Nullable data is the same length as nonnull data.

Data typeDefault length (characters)
charLength defined for the column
varcharLength defined for the column
ncharTwice the length defined for the column
nvarcharTwice the length defined for the column
Text0
ntext0
bit1
binaryTwice the length defined for the column + 1
varbinaryTwice the length defined for the column + 1
image0
datetime24
smalldatetime24
float30
real30
int12
bigint19
smallint7
tinyint5
money30
smallmoney30
decimal41*
numeric41*
uniqueidentifier37
timestamp17
varchar(max)0
varbinary(max)0
nvarchar(max)0
UDTLength of the user-defined term (UDT) column
XML0

*For more information about the decimal and numeric data types, see decimal and numeric (Transact-SQL).

System_CAPS_ICON_note.jpg Note


A column of type tinyint can have values from 0 through 255; the maximum number of characters that are needed to represent any number in that range is three (representing values 100 through 255).

Default Field Lengths for Native File Storage

The following table lists the default field lengths for data to be stored as native file storage type. Nullable data is the same length as nonnull data, and character data is always stored in character format.

Data typeDefault length (characters)
bit1
binaryLength defined for the column
varbinaryLength defined for the column
image0
datetime8
smalldatetime4
float8
real4
int4
bigint8
smallint2
tinyint1
money8
smallmoney4
decimal*
numeric*
uniqueidentifier16
timestamp8

*For more information about the decimal and numeric data types, see decimal and numeric (Transact-SQL).

In all of the preceding cases, to create a data file for later reloading into SQL Server that keeps the storage space to a minimum, use a length prefix with the default file storage type and the default field length.

bcp Utility
Data Types (Transact-SQL)
Specify Field and Row Terminators (SQL Server)
Specify Prefix Length in Data Files by Using bcp (SQL Server)
Specify File Storage Type by Using bcp (SQL Server)
Keep Nulls or Use Default Values During Bulk Import (SQL Server)

Community Additions

ADD
Show:
© 2016 Microsoft