Using Native, Character, and Unicode Formats

Administering SQL Server

Administering SQL Server

Using Native, Character, and Unicode Formats

The bcp utility can create or read data files in the following default data formats by specifying a switch at the command prompt.

Data format bcp utility switch BULK INSERT clause
Native -n DATAFILETYPE = 'native'
Character -c DATAFILETYPE = 'char'
Unicode character -w DATAFILETYPE = 'widechar'
Unicode native -N DATAFILETYPE = 'widenative'

By default, the bcp utility operates in interactive mode and queries Microsoft® SQL Server™ and the user for information required to specify the data format. However, when using the -n, -c, -w, or -N switches, bcp does not query for information about the SQL Server table on a column-by-column basis. It reads or writes the data using the default format specified.

By default, the BULK INSERT statement operates in character mode (char). Interactive mode does not apply.

Additionally, the –V switch causes the bcp utility to modify native (-n) or character (-c) data to a format compatible with earlier versions of SQL Server clients. For more information, see Copying Native and Character Format Data from Earlier Versions of SQL Server.

Native mode bulk copies are best for sql_variant columns. Unlike character or Unicode bulk copies, native mode bulk copies preserve the meta data for each sql_variant value.

The recommended default data format depends on the type of bulk copy operation.


Bulk copy operation

Native

Character
Unicode character Unicode native
Bulk copying data between multiple instances of SQL Server using a data file (no extended/double-byte character set (DBCS) characters involved). YES 1 -- -- --
Bulk copying data between multiple instances of SQL Server using a data file (extended/DBCS characters involved). -- -- -- YES
Exporting data to a text file to be used in another program. -- YES -- --
Importing data from a text file generated by another program. -- YES -- --
Bulk copying data between multiple instances of SQL Server using a data file (Unicode data/no extended/DBCS characters). -- -- YES --

1 Fastest method for bulk copying data from SQL Server using bcp.

See Also

Specifying Data Formats