Export (0) Print
Expand All

Prefix Length

SQL Server 2000

Prefix Length

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 or image data type requires four prefix characters to store the field length, whereas a varchar 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 an instance of 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 requires a prefix length of 1 or greater, depending on the file storage type.

Use these prefix lengths when bulk copying data from an instance of 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
bigint 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 an instance of 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.

Show:
© 2014 Microsoft