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 |
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 |
smallint | 2 |
tinyint | 1 |
money | 8 |
smallmoney | 4 |
decimal | *See footnote |
numeric | *See footnote |
uniqueidentifier | 16 |
timestamp | 8 |
For information about the decimal and numeric data types, see decimal and numeric. |
Accepting the bcp default values for the field length is recommended.
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:
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... |