Problem with insert Thai Language data to SQL Server 2008 field datatype text and show ????

Question

Hello everyone

I created MVC ASP.Net Web application and tried insert Thai language data to SQL Server 2008 database to field with data type text and then database store ?????? which is incorrect. For Html Page I user charset utf-8

However I tried to Encode string before insert data to database and change database field collation. These do not solve problem.

I'm looking forward to your reply.

Thanks,

embarus

Answer 1

Try using the NVARCHAR(MAX) datatype. This may solve the problem. What error are you getting?

Answer 2

SQL Server cannot natively store UTF-8. If you want to store UTF-8 data you have a few choices:

1. Store it in Unicode (nvarchar) and convert it to UTF-8 on the client. It should be noted that the SQL Server driver will already convert most strings to Unicode. The obvious downside is that it eats up twice the space. However, space is cheap and this is by far the simplest solution and the least headache.
2. Store it in binary and convert it to UTF-8 on the client (which pretty much eliminates any sorting done on the server and all your searches will be culture and case agnostic)
3. Create a CLR user defined type in SQL Server to store UTF-8 values. Microsoft provides a sample that comes with SQL Server to do just this. You can download the samples from CodePlex from here. You can also find more information on the sample in this article in the Books Online. The downside is that you have to have the CLR enabled in SQL Server and I'm not sure how well it will perform.