August 19, 2019 at 12:00 am
Comments posted to this topic are about the item Storing JSON data
August 19, 2019 at 5:42 am
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”
August 19, 2019 at 6:47 am
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.
August 19, 2019 at 9:17 am
Am I blind? I only see references to NVARCHAR in the linked article, no VARCHAR.
August 19, 2019 at 10:33 am
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.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply