Storing JSON data

  • Comments posted to this topic are about the item Storing JSON data

  • Interesting.

    I've always only stored JSON as NVARCHAR...

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • LOB storage - JSON documents can be stored as-is in NVARCHAR columns. This is the best way for quick data load and ingestion because the loading speed is matching loading of string columns. This approach might introduce additional performance penalty on query/analysis time if indexing on JSON values in not performed, because the raw JSON documents must be parsed while the queries are running.



  • Am I blind? I only see references to NVARCHAR in the linked article, no VARCHAR.

  • No, I only see NVARCHAR there, too. But this article talks about VARCHAR

  • Although the article does only use an nvarchar, varchar does work as well. Any of the examples in the documentation work exactly the same if you use a varchar variable/column and remove the N prefix from the string literal declarations.

    SQLServer does return an nvarchar(MAX) as well when using FOR JSON AUTO (see below example), however, again that doesn't mean you can't store it in a varchar. Just like with any string storage in SQL Server, there's little point in using an nvarchar if your values are never going to have a character out of the collation's ASCII range.


    CREATE TABLE dbo.test (value1 varchar(10),
    value2 int);

    INSERT INTO dbo.test (value1,


    SELECT system_type_name
    FROM sys.dm_exec_describe_first_result_set('SELECT * FROM dbo.test FOR JSON AUTO;',NULL,0);

    DROP TABLE dbo.test


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply