What's best SQL datatype for storing JSON string?

What's the best SQL datatype for storing JSON string?

static List<ProductModel> CreateProductList()
{
    string json = @"[
        {
            ProductId: 1, 
            ProductCode: 'A', 
            Product: 'A'
        },
        {
            ProductId: 2, 
            ProductCode: 'B', 
            Product: 'B'
        }
    ]";

    IList<JToken> tokenList = JToken.Parse(json).ToList();
    List<ProductModel> productList = new List<ProductModel>();

    foreach (JToken token in tokenList)
    {
        productList.Add(JsonConvert.DeserializeObject<ProductModel>(token.ToString()));
    }

    return productList;
}

Which SQL datatype should we use for storing such a string containing JSON?

• NVARCHAR(255)?
• TEXT?
• VARBINARY(MAX)?

Certainly NOT:

• TEXT, NTEXT: those types are deprecated as of SQL Server 2005 and should not be used for new development. Use VARCHAR(MAX) or NVARCHAR(MAX) instead

• IMAGE, VARBINARY(MAX) : IMAGE is deprecated just like TEXT/NTEXT, and there's really no point in storing a text string into a binary column....

So that basically leaves VARCHAR(x) or NVARCHAR(x): VARCHAR stores non-Unicode strings (1 byte per character) and NVARCHAR stores everything in a 2-byte-per-character Unicode mode. So do you need Unicode? Do you have Arabic, Hebrew, Chinese or other non-Western-European characters in your strings, potentially? Then go with NVARCHAR

The (N)VARCHAR columns come in two flavors: either you define a maximum length that results in 8000 bytes or less (VARCHAR up to 8000 characters, NVARCHAR up to 4000), or if that's not enough, use the (N)VARCHAR(MAX) versions, which store up to 2 GByte of data.

Update: SQL Server 2016 will have native JSON support - a new JSON datatype (which is based on nvarchar) will be introduced, as well as a FOR JSON command to convert output from a query into JSON format

Update #2: in the final product, Microsoft did not include a separate JSON datatype - instead, there are a number of JSON-functions (to package up database rows into JSON, or to parse JSON into relational data) which operate on columns of type NVARCHAR(n)

I shall go for nvarchar(max). That should fit the requirement.

Update: With SQL Server 2016 and Azure SQL, there are a lot of additional native JSON capabilities. This might positively impact your design or approach. You may read this for more: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

I would recommend to use nvarchar(max) if you plan to use JSON features on SQL 2016 or Azure SQL.

If you don't plan to use those features, you could use varbinary(max) combined with COMPRESS (and DECOMPRESS) functions. More information: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

> COMPRESS and DECOMPRESS functions use standard GZip compression. If your client can handle GZip compression (e.g browser that understands gzip content), you can directly return compressed content. Note that this is performance/storage trade-off. If you frequently query compressed data you mig have slower performance because text must be decompressed each time.

Recommended data type is NVARCHAR.
Please refer https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15#store-and-index-json-data-in-sql-server][1]