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.
Data types like varchar, char and nvarchar are all used to store string data in SQL Server.
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With