Storage of Data in Character Format

How character-formatted data is stored in an exported data file depends on how the data was stored in the Microsoft SQL Server table. Two storage patterns are significant, as follows:

• SQL Server char data
• Other data types converted to char storage

Storage Patterns for SQL Server char Data

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. If the char data is shorter than the field length, spaces are appended to the data as padding; for more information, see SET ANSI_PADDING (Transact-SQL). However, for the varchar type, only the actual number of characters is stored unless you choose either a prefix or a terminator.

Storage Patterns for Other Data Types Converted to char Data

The amount of storage space that is allocated in the data file for noncharacter data stored in character format is determined by the combination of field length, prefix length, and terminators, as follows:

• With a prefix length of 0 and no terminator, the field is treated as if it were of fixed length, as specified by the field-length value. If specifying a prefix length of 0 and no terminator, the bcp utility allocates the maximum amount of space shown in the field length prompt because this is the maximum space that may be needed for the data type in question. This practice makes it is possible to determine where one field ends and the next begins.

• With a prefix length of 0 and a terminator, the field-length value is ignored. The storage space used by the field is the length of the data, plus any terminator.

  Important

  Specifying both a prefix and a terminator is supported only in non-XML format files.

• With a prefix length of 1, 2, or 4, the field-length value is ignored. The space used by the field is its length, the length of the field prefixes, plus any terminators.

In all of the cases in the preceding list, you can create a data file for later reloading into SQL Server that keeps the storage space to a minimum. To minimize storage space, use a length-prefix character along with the default file storage type and the default field length.

Examples

The following examples illustrate the interaction among field length, prefix length, and terminators when data is stored in char format. A prefix-length value, if present, is represented by P. A terminator, if present, is represented by T. An ellipsis (...) indicates that the pattern repeats for every field.

Examples of storage patterns for SQL Server char data:

The table schema is t1 (c1 char(8)). The examples assume a 5-character value (represented as Hello), which is stored in a field whose length is 8 characters. In each of the example data fields, three spaces must always be appended to the 5-character Hello value. The appended spaces are represented by three dots (∙∙∙).

Prefix length = 0

Prefix length = 1, 2, or 4

No terminator:

Hello∙∙∙Hello∙∙∙...

PHello∙∙∙PHello∙∙∙...

Terminator:

Hello∙∙∙THello∙∙∙T...

PHello∙∙∙TPHello∙∙∙T...

Note:

This example applies only to non-XML format file.

Examples of storage patterns for noncharacter data types when converted to char storage

The table schema is t1 (c1 int),and each row has an integer value of 999.

Prefix length = 0

Prefix length = 1, 2, or 4

No terminator:

999∙∙999∙∙...

P999P999...

Terminator:

999T999T...

P999TP999T...

Note:

This example applies only to non-XML format file.

See Also

Concepts

Specifying Field Length by Using bcp
Specifying File Storage Type by Using bcp
Specifying Prefix Length in Data Files
Specifying Field and Row Terminators

Other Resources

bcp Utility
Data Types (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History