Field Length
When bulk copying char, nchar, or binary data with a prefix length of 0 from Microsoft® SQL Server™, bcp also prompts for a field length. The field length indicates the maximum number of characters needed to represent data in character format. A column of type tinyint can have values from 0 through 255; the maximum number of characters needed to represent any number in that range is three (representing values 100 through 255). When bcp converts noncharacter data to character, it suggests a default field length large enough to store the data.
If the file storage type is noncharacter, data is stored in the SQL Server native data representation (native format) and the bcp utility does not prompt for a field length.
These are the default field lengths for data to be stored as char 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 |
*For more information about the decimal and numeric data types, see decimal and numeric.
These are 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 |
| decimal | * |
| numeric | * |
| uniqueidentifier | 16 |
| timestamp | 8 |
*For more information about the decimal and numeric data types, see decimal and numeric.
Accepting the bcp default values for the field length is recommended.
Note Using default data type sizes (field length) can lead to an "unexpected end of file" error message. This generally occurs with the money and datetime data types when only part of the field occurs in the data file (for example, a datetime value of mm/dd/yy with no time component) rather than an entire string, as expected by SQL Server. When using the default size option, SQL Server expects to read 24 characters (the length of the datetime data type when stored in char format). To avoid this problem, bulk copy data using field terminators, or fixed-length data fields.
Specifying a field length too short for numeric data when bulk copying data causes bcp to print an overflow message and not copy the data. When datetime data is copied to a data file as a character string of less than 26 bytes, the data is truncated without an error message. When creating an ASCII data file, use the default field length to ensure that data is not truncated and that numeric overflow errors causing bcp to fail do not occur. To change the default field length, supply another value.
Note To create a data file for later reloading into SQL Server and keep the storage space to a minimum, use a length prefix character with the default file storage type and the default field length.
The amount of storage space allocated in the data file for noncharacter data stored as char file storage type also depends on whether a prefix length or terminators are specified:
- If specifying a prefix length of 1, 2, or 4, the field length is not used. The data file storage space used is the length of the data, the length of the prefix, plus any terminators.
- If specifying a prefix length of 0 and no terminator, bcp allocates the maximum amount of space shown in the field length prompt because this is the maximum space that may be needed for the data type in question. 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.
- If specifying a prefix length of 0 and a terminator, the field length specified is ignored. The data file storage space used is the length of the data, plus any terminators.
SQL Server char data is always stored in the data file as the full length of the defined column. For example, a column defined as char(10) always occupies 10 characters in the data file regardless of the length of the data stored in the column; spaces are appended to the data as padding. For more information, see SET ANSI_PADDING.
The interaction of prefix lengths (P), terminators (T), and field length on data determines the storage space used in the data file. In this example, the field length is 8 for each column, and the 6-character value "string" is stored each time. Dashes (-) indicate appended spaces and ellipses (...) indicate that the pattern repeats for each field.
This is the pattern for SQL Server char data.
| Prefix length = 0 | Prefix length = 1, 2, or 4 | |
|---|---|---|
| No terminator | string--string--... | Pstring--Pstring--... |
| Terminator | string--Tstring--T... | Pstring--TPstring--T... |
This is the pattern for other data types converted to char storage.
| Prefix length = 0 | Prefix length = 1, 2, or 4 | |
|---|---|---|
| No terminator | string--string--... | PstringPstring... |
| Terminator | stringTstringT... | PstringTPstringT... |