sql server - What's best SQL datatype for storing JSON string?

Question

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)?

Answer 1

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)