Can we Store JSON data as varbinary in SQL server?

Dev_Fuel07 21 Reputation points
2022-04-04T05:34:48.323+00:00

I am only creating a staging table to temporary work with JSON data in memory, therefore avoiding it to store in a column per field manner. Instead I would like to store that variable json values in SQL DB entity. I am stuck how to store it?

I am using .Net Framework - C# MVC - EntityFramework 6.2

By Default Entity Framework will generate nvarchar(max) type for JSON string but I have observed that nvarchar(max) will not retrieve all data only a limited chunk of it and that will lead to JSON deserialization failure in memory.

I want to avoid this and store large JSON data as varbinary, JSON object will have 200 fields (key value pairs) some of them might hold more than 500 alphanumeric characters.

How to set datatype for such scenario from entity framework code first approach, Any suggestions?

Developer technologies | .NET | Other
Developer technologies | Transact-SQL
Developer technologies | ASP.NET | Other
SQL Server | Other
{count} votes

Accepted answer
  1. Olaf Helper 47,516 Reputation points
    2022-04-04T05:51:24.503+00:00

    Sure you can store JSON as varbinary; but that don't make much sense. JSON is string and so nvarchar(max) is the better choice.
    All JSON functions in T-SQL work with nvarchar, not with binary, see https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

    but I have observed that nvarchar(max) will not retrieve all data only a limited chunk of it

    Then you are doing something wrong, it works for me and all others.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
    2022-04-04T21:31:13.28+00:00

    If you store the data as nvarchar(MAX) there should be any problem in retrieving it. But I seem to recall that there is an issue if you use FOR JSON PATH. Then you need to retrieve the JSON with a stream reader, of which I do not recall the details.

    But you can work around this by saying:

    SET @json = (SELECT ... FOR JSON PATH)
    SELECT @json AS MyJson
    
    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.