Specifying the prefix length in a data file by using bcp

using bcp Specifies the prefix length in the data file

When bulk exporting data to a data file in native format , the bcp command uses one or more characters before each field to indicate the length of the field in order to make the file storage space the most compact . These characters are called "length prefix characters".

bcp the prefix length hint

If an interactive bcp command contains an in or out option without the format file switch (-f) or data format switch (-N,-C,-W, or-N), the command prompts for the prefix length for each data field, as follows:

Enter prefix length of field <field_name> [<default>]:

If you specify 0, bcp prompts for the length of the field (for character data types) or for the field terminator (for native non-character types).

Attention

After you interactively specify all the fields in a bcp command, the command prompts you to save your own responses to each field in a non-XML format file.

Prefix length Overview

To store the prefix length of a field, you need to have enough bytes to represent the maximum length of the field. In addition, the number of bytes required depends on the file storage type, whether the column can be Null, and if the data is stored in a data file in native or character format. For example, the text or image data type requires four prefix characters to store the field length, whereas 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 Notes

Use the length prefix instead of the field terminator when using native formats. Native format data may conflict with Terminators because the native format data file is stored in the internal binary data format of SQL Server.

Prefix length when bulk export

Attention

The default value provided in the prefix length hint when the field is exported indicates the most effective prefix length for the field.

Null value is represented by an empty field. To indicate the character blank (which represents NULL), the field prefix should contain a value of 1, that is, the field prefix requires at least 1 bytes. Note that if a SQL Server table column allows Null values, the column requires a prefix length of 1 or greater, depending on the type of file storage.

When you bulk export data and store data in a native data type or character format, use the prefix length shown in the following table.

SQL Server Data type	Native format Non-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 (user-defined data type)	8	8	8	8
Xml	8	8	8	8

The ntext, text, and image data types will be removed in future versions of SQL Server. Avoid using these data types in new development work, and consider modifying applications that currently use these data types. Use nvarchar (max), varchar (max), and varbinary (max) instead.

Prefix length at Bulk Import

When bulk importing data, the prefix length is the value specified when the data file was originally created. If the data file is not created by a bcp command, the length prefix character may not exist. In this case, the prefix length is specified as 0.

Attention

To specify the prefix length in a data file that is not created by using bcp , use the length provided in the prefix length for bulk export earlier in this topic.

Reference:

https://technet.microsoft.com/zh-CN/library/ms190779

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1628109

Specifying the prefix length in a data file by using bcp