Specifying Field Length by Using bcp
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.
The bcp Prompt for Field Length
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 Specifying Data Formats for Compatibility by Using bcp.
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 Understanding Non-XML Format Files.
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).
Using Default Field Lengths
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.
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 type |
Default length (characters) |
|---|---|
char |
Length defined for the column |
varchar |
Length defined for the column |
nchar |
Twice the length defined for the column |
nvarchar |
Twice the length defined for the column |
Text |
0 |
ntext |
0 |
bit |
1 |
binary |
Twice the length defined for the column + 1 |
varbinary |
Twice the length defined for the column + 1 |
image |
0 |
datetime |
24 |
smalldatetime |
24 |
float |
30 |
real |
30 |
int |
12 |
bigint |
19 |
smallint |
7 |
tinyint |
5 |
money |
30 |
smallmoney |
30 |
decimal |
41* |
numeric |
41* |
uniqueidentifier |
37 |
timestamp |
17 |
varchar(max) |
0 |
varbinary(max) |
0 |
nvarchar(max) |
0 |
UDT |
Length of the user-defined term (UDT) column |
XML |
0 |
*For more information about the decimal and numeric data types, see decimal and numeric (Transact-SQL).
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 type |
Default length (characters) |
|---|---|
bit |
1 |
binary |
Length defined for the column |
varbinary |
Length defined for the column |
image |
0 |
datetime |
8 |
smalldatetime |
4 |
float |
8 |
real |
4 |
int |
4 |
bigint |
8 |
smallint |
2 |
tinyint |
1 |
money |
8 |
smallmoney |
4 |
decimal1 |
* |
numeric1 |
* |
uniqueidentifier |
16 |
timestamp |
8 |
1 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.