Using Native, Character, or Unicode Formats

This section discusses the data formats that are supported for the bulk export or bulk import of data. Microsoft SQL Server can accept data in character or native binary format. Use character format when you move data between SQL Server and another application (such as Microsoft Excel) or another database server (such as Oracle or SQL Server). You can use native format only when you transfer data between instances of SQL Server.

Choosing a Data Format

The following table provides general guidelines for choosing a data format depending on how the data is represented and the source or target of the operation.

Operation Native Unicode native Character Unicode character

Bulk transfers of data between multiple instances of SQL Server by using a data file that does not contain any extended or double-byte character set (DBCS) characters. Unless a format file is used, these tables must be identically defined.

See Using Native Format to Import or Export Data.

Yes1

For sql_variant columns, use of native data format is best, because native data format preserves the metadata for each sql_variant value, unlike character or Unicode formats.

See Using Native Format to Import or Export Data.

Yes

Bulk transfers of data between multiple instances of SQL Server by using a data file that contains extended or DBCS characters.

See Using Unicode Native Format to Import or Export Data.

Yes

Bulk import of data from a text file that is generated by another program.

See Using Character Format to Import or Export Data.

Yes

Bulk export of data to a text file that is to be used in another program.

See Using Character Format to Import or Export Data.

Yes

Bulk transfers of data between multiple instances of SQL Server by using a data file that contains Unicode data and does not contain any extended or DBCS characters.

See Using Unicode Character Format to Import or Export Data.

Yes

1 Fastest method for the bulk export of data from SQL Server when using bcp.

See Also

Concepts

Scenarios for Bulk Importing and Exporting Data
Specifying Data Formats for Compatibility by Using bcp
Working with Unicode Data

Other Resources

Data Types (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance