What data type should be correct to store JSON content as string?

Question

I plan to use JSON.NET to serialize and deserialize objects to and with the json string store it in the database.

What datatype of the SQL Server 2008 should be the best to store it?

• nvarchar(max)?
• varchar(max)?

Answer 1

Will your json be multilingual? If yes - go with nvarchar. If not - go with varchar, it uses less storage space and is more efficient in terms of performance.

Answer 2

You should probably use nvarchar(max).

http://www.ietf.org/rfc/rfc4627.txt

The preferred encoding for JSON is UTF-8, but UTF-16 and UTF-32 are also possible, so you may be getting data which would include wide characters.

On the other hand, you could also use varbinary(max), store the actual UTF-8 stream and save some space and processing since the UTF-8 stream wouldn't need to be converted to an actual string in .NET (i.e. no UTF-8 to Unicode internal .NET string).

I would not use varchar(max) because if you do have wide characters coming in then they could be misinterpreted by any queries which are not converting the UTF-8 which happens to be stored in varchar properly (assuming 8-bit varchar because you can also store multi-byte in varchar depending upon your database settings), and you wouldn't have to worry about converting the UTF-8 to the particular code page for your varchar. Again, nvarchar is Unicode so you wouldn't have to worry about any of that mess.

Are you going to do any operations on the JSON in SQL Server or just storage?