Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's best SQL datatype for storing JSON string?

People also ask

Which data type of SQL is used to store a JSON object?

JSON text is stored in VARCHAR or NVARCHAR columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features.

Which data type is used to store string in SQL?

Data types like varchar, char and nvarchar are all used to store string data in SQL Server.

Does SQL Server have JSON data type?

Summary. All modern web applications support JSON and it is one of the well-known data interchange formats. Now, SQL Server also supports the JSON format. There is no specific data type for JSON SQL Server like XML.


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)


I shall go for nvarchar(max). That should fit the requirement.

Update: With SQL Server 2016 and Azure SQL, there are a lot of additional native JSON capabilities. This might positively impact your design or approach. You may read this for more: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server


I would recommend to use nvarchar(max) if you plan to use JSON features on SQL 2016 or Azure SQL.

If you don't plan to use those features, you could use varbinary(max) combined with COMPRESS (and DECOMPRESS) functions. More information: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

COMPRESS and DECOMPRESS functions use standard GZip compression. If your client can handle GZip compression (e.g browser that understands gzip content), you can directly return compressed content. Note that this is performance/storage trade-off. If you frequently query compressed data you mig have slower performance because text must be decompressed each time.


Recommended data type is NVARCHAR.
Please refer https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15#store-and-index-json-data-in-sql-server][1]