Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
To provide the most compact file storage for the bulk export of data in native format to a data file, the bcp command precedes each field with one or more characters that indicates the length of the field. These characters are called length prefix characters.
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 prefix length of each data field, as follows:
Enter prefix length of field <field_name> [<default>]:
If you specify 0, bcp prompts you for either the length of the field (for a character data type) or a field terminator (for a native non-character type).
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 about non-XML format files, see Non-XML Format Files (SQL Server).
To store the prefix length of a field, you need enough bytes to represent the maximum length of the field. The number of bytes that are required also depends upon the file storage type, the nullability of a column, and whether the data is being stored in the data file in its native or character format. For example, a text or image data type requires four prefix characters to store the field length, but a varchar data type requires two characters. In the data file, these length-prefix characters are stored in the internal binary data format of SQL Server.
Important
When you use native format, use length prefixes rather than field terminators. Native format data might conflict with terminators because a native-format data file is stored in the SQL Server internal binary data format.
Note
The default value that is provided at the prefix-length prompt when you export a field indicates the most efficient prefix length for the field.
Null values are represented as an empty field. To indicate that the field is empty (represents NULL), the field prefix contains the value -1; that is, it requires at least 1 byte. Note that if a SQL Server table column allows null values, the column requires a prefix length of 1 or greater, depending on the file storage type.
When you bulk export data and store it in either native data types or character format, use the prefix lengths shown in the following table.
SQL Server data type |
Native format NOT NULL |
Native format NULL |
Character format NOT NULL |
Character format 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 | 4 | 4 |
binary | 2 | 2 | 2 | 2 |
varbinary | 2 | 2 | 2 | 2 |
image* | 4 | 4 | 4 | 4 |
datetime | 0 | 1 | 0 | 1 |
smalldatetime | 0 | 1 | 0 | 1 |
decimal | 1 | 1 | 1 | 1 |
numeric | 1 | 1 | 1 | 1 |
float | 0 | 1 | 0 | 1 |
real | 0 | 1 | 0 | 1 |
int | 0 | 1 | 0 | 1 |
bigint | 0 | 1 | 0 | 1 |
smallint | 0 | 1 | 0 | 1 |
tinyint | 0 | 1 | 0 | 1 |
money | 0 | 1 | 0 | 1 |
smallmoney | 0 | 1 | 0 | 1 |
bit | 0 | 1 | 0 | 1 |
uniqueidentifier | 1 | 1 | 0 | 1 |
timestamp | 1 | 1 | 1 | 1 |
varchar(max) | 8 | 8 | 8 | 8 |
varbinary(max) | 8 | 8 | 8 | 8 |
UDT (a user-defined data type) | 8 | 8 | 8 | 8 |
XML | 8 | 8 | 8 | 8 |
sql_variant | 8 | 8 | 8 | 8 |
*The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
When data is bulk imported, the prefix length is the value that was specified when the data file was created originally. If the data file was not created by a bcp command, length prefix characters probably do not exist. In this instance, specify 0 for the prefix length.
Note
To specify a prefix length in a data file that was not created by using bcp, use the lengths provided in Prefix Lengths for Bulk Export, earlier in this topic.
bcp Utility
Data Types (Transact-SQL)
Specify Field Length by Using bcp (SQL Server)
Specify Field and Row Terminators (SQL Server)
Specify File Storage Type by Using bcp (SQL Server)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Documentation
Specify file storage type with bcp - SQL Server
Use bcp to export data to a file as its database table type, in its character representation, or as a data type that supports implicit conversion.
Specify compatibility data formats with bcp - SQL Server
For bulk exports in SQL Server with bcp, data formats might be incompatible with expected layout. A non-xml format file specifies compatibility data formats.
Specify Field Length by Using bcp (SQL Server) - SQL Server
In SQL Server, if necessary, bcp prompts for field length, default field lengths, and impact of field-length on data storage in files that contain char data.