Search code examples
sql-serverbcp

SQL Server bcp parameter, native, character, Unicode native, Unicode character, when to use each?

I'm reading this page that describing the bcp utility. It states that:

This section contains the following examples that show how to use bcp commands to create a non-XML format file:

A. Creating a non-XML format file for native data

B. Creating a non-XML format file for character data

C. Creating a non-XML format file for Unicode native data

D. Creating a non-XML format file for Unicode character data

The examples use the HumanResources.Department table in the AdventureWorks2012 sample database. The HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

I'm not clear what does these types mean? When to use each?

Thanks.

Solution

  • There are two dimensions:

    • native vs. character. Native format creates a binary file. Character format creates a text file. Use character when you want the result to be human readable and usable for other apps (eg. import in Excel). Use native if both the source and destinations are SQL Server and human readability is not desired/needed.

    • Unicode vs. non-Unicode. Unicode will store strings in wide format (Unicode encoding). non-Unicode will store them in the specified code page encoding (the -C argument). If space is not a concern, use Unicode, unless you enjoy pain.

    You have to realize that you're seeing a product with +20 years history behind, there is path dependence. Nowadays I always use native Unicode (-n -w) unless I have specific reasons not to.