Storing JSON data

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715401

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71362

    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”

  • ako58

    Hall of Fame

    Points: 3222

    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.

     

     

  • ildjarn.is.dead

    Say Hey Kid

    Points: 691

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

  • Wolfram Kraus

    SSCommitted

    Points: 1845

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

  • Thom A

    SSC Guru

    Points: 98307

    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.

    Example:

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

    GO
    INSERT INTO dbo.test (value1,
    value2)
    VALUES('abc',1),
    ('def',2);

    GO


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

    DROP TABLE dbo.test

    Thom~

    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