To provide the most compact file storage when bulk copying data in native format to a data file, bcp precedes each field with one or more characters that indicates the length of the field. These characters are called length prefix characters. The number of length prefix characters required is called the prefix length.
The number of length prefix characters required to store the length of the data field depends on the file storage type, the nullability of a column, and whether the data is being stored in the data file in its native (database) data type or as ASCII characters (character format). A text data type requires four prefix characters to store the field length, whereas a binary data type requires two characters.
Note These length prefix characters are stored in the data file in Microsoft® SQL Server™ internal binary data format.
Null values are represented as an empty field when copied from SQL Server to a data file. To indicate that the field is empty (represents NULL), the field prefix contains the value -1. Any SQL Server column that allows null values needs field prefix characters, and requires a prefix length of 1 or greater, depending on the file storage type.
Use these prefix lengths when bulk copying data from SQL Server to a data file, storing the data using either native data types or as ASCII characters (text file).
| SQL Server | Native format | Character format | ||
|---|---|---|---|---|
| data type | NOT NULL | NULL | NOT NULL | NULL |
| char | 2 | 2 | 2 | 2 |
| varchar | 2 | 2 | 2 | 2 |
| nchar | 2 | 2 | 2 | 2 |
| nvarchar | 2 | 2 | 2 | 2 |
| text | 4 | 4 | 4 | 4 |
| ntext | 4 | 4 | 1 | 1 |
| binary | 1 | 1 | 2 | 2 |
| varbinary | 1 | 1 | 2 | 2 |
| image | 4 | 4 | 4 | 4 |
| datetime | 0 | 1 | 1 | 1 |
| smalldatetime | 0 | 1 | 1 | 1 |
| decimal | 1 | 1 | 1 | 1 |
| numeric | 1 | 1 | 1 | 1 |
| float | 0 | 1 | 1 | 1 |
| real | 0 | 1 | 1 | 1 |
| int | 0 | 1 | 1 | 1 |
| smallint | 0 | 1 | 1 | 1 |
| tinyint | 0 | 1 | 1 | 1 |
| money | 0 | 1 | 1 | 1 |
| smallmoney | 0 | 1 | 1 | 1 |
| bit | 0 | 1 | 0 | 1 |
| uniqueidentifier | 1 | 1 | 1 | 1 |
| timestamp | 1 | 1 | 2 | 2 |
When storing data as nchar rather than char, the prefix length for all data types is the same as the native data type value, except char, varchar, text, ntext, and image, which all have a prefix length of 1.
When bulk copying data to SQL Server, the prefix length is the value specified when the data file was created originally. If the data file was not created with bcp, it is unlikely that length prefix characters exist. In this instance, specify 0 for the prefix length.
Note The default values provided at the prompts indicate the most efficient prefix lengths.