Export table with nvarchar to CSV

Question

Fellow DBA's
I am going to need to export some tables to csv. Some of those tables contain nvarchar in "some" of the columns.
Is it correct to interpret that by doing this, i will also need to save the csv file out with unicode?

Thanks,
MG

Answer 1

Right, nvarchar stores text in Unicode encoding. Tools like BCP and SqlCmd supports to write to Unicode text files, see
https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16

Answer 2

According to MSFT docs : Yes, under some circumstances that might be the recommended way to store those "text"-columns.

Unicode character format is recommended for bulk transfer of data between multiple instances of SQL Server by using a data file that contains extended/DBCS characters. The Unicode character data format allows data to be exported from a server by using a code page that differs from the code page used by the client that is performing the operation. In such cases, use of Unicode character format has the following advantages:

If the source and destination data are Unicode data types, use of Unicode character format preserves all of the character data.

If the source and destination data are not Unicode data types, use of Unicode character format minimizes the loss of extended characters in the source data that cannot be represented at the destination.

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/use-unicode-character-format-to-import-or-export-data-sql-server?view=sql-server-ver16

Answer 3

The answer is: maybe. You should export to UTF-16 or UTF-8 be sure that no characters are mutilated. But if you feel confident that all data is in your ANSI code page (presumably CP1252), you can go for an ANSI file.