In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar.
Most of our JSON strings will not use more than 100 characters.
Should I use nvarchar(100) or nvarchar(max)?
We prefer nvarchar(100). I just want to make sure the new JSON functions do not have a preference for using the max type. Max is stored outside of row and slightly less efficient performance-wise.
The documentation you linked to links to the JSON page, which says:
JSON is a textual format so the JSON documents are can stored in NVARCHAR columns in SQL Database.
Wait a second...I think this documentation needs a pull request :)
Anyways...As always with MAX, if you don't need it, don't use it! You're correct that it will cause a (slight) performance hit and SQL Server in no way prefers NVARCHAR(MAX) for JSON storage. Consider yourself lucky you're storing such small JSON.
Currently, only a text-type is supported. Microsoft SQL suggests NVARCHAR(max) for storage.
Microsoft SQL does not currently have a binary JSON type. So all you'll ever get is validated text, natively. Alternatively you can use the C# CLR UTD to link into JSON.net. From the Microsoft blog
If you believe that JSONB format from PostgreSQL or some compressed format like zipped JSON text is better option, you can parse JSON text in UDT, store it as JSONB in some binary property of CLR UTD, and create member methods that can use properties from that format.
They go on to say,
Currently we have not found that anyone even tried to create CLR UDT that encapsulate JSONB format, so we will not have that kind of experiments in this version.
Which doesn't sound encouraging. Part of the problem is they don't see a need for it, from the docs.
Our focus will be on providing good functionalities and query optimization and not on storage. We know that PostgreSQL has a native type and JSONB support, but we still don't know is this faster or better than CLR alternatives so in this version we want to focus on the other things that are more important (do you want to see SQL Server with native type but without built-in functions that handle JSON – I don’t think so 🙂 ). However, we are open for suggestions and if you believe the native type that can replace CLR JSON or plain text will help, you can create request on connect site so we can discuss it there. Again, our choice to start with FOR JSON and OPENJSON is the fact that these functionalities are requested in JSON connect item and probably only things that cannot be easily implemented with CLR.
Part of this is because like MySQL developers, SQL Server developers do not understand the advantages of binary types which include,
They say,
So, our focus is on the export/import and some built-in functions for JSON processing. Someone might say - this will not be fast enough but we will see. We will talk about performance once we ship functionalities and improve performance if needed. However, be aware that built-in JSON parser is the fastest way to process JSON in database layer. You might use CLR type or CLR parsers as external assemblies but this will not be better than the native code that parses JSON.
Which is basically just a tldr that C# is slower and their "native code" in C++ will be faster at parsing, though not at storage and retrieval.
If you need a binary JSON type, consider PostgreSQL. It's referenced multiple times by Microsoft themselves, it's also free and open source.
