I'm attempting to implement a database using nvarchar datatypes. I will need to insert and extract data (programmatically) via SQL queries and bulk loads.
So, my question is, is there any way to insert data into nvarchar columns in a UTF-8 encoding? From the documentation I've read, it seems like internal storage for nvarchars is UTF-16. I can concieve that the imputing may not be a problem since the database may know how to convert between the two. When retrieving the data though, how will SqlServer know what kind of coding to use when returning my data? Is there some collation property I can set that effectually says 'SqlServer, I will be presenting you data in XXXXX encoding. Please do the same for me.' I'm worried that data I will be getting back from nvarchar columns will be in UTF-16, and my legacy program using the data can't handle the embeded nulls that come along with UTF16.
By default, encoding for the result is set to UTF-8 and I believe it does matter with storage space being UTF16 is stored as 2 * 2 =4, and its inefficient in terms of performance of process in large database.