How to increase size of nvarchar(max) in sql server ms

Question

Let us assume a table with below definition:
Create table demo (json_data nvarchar(max));

I loaded the json format data into this for a row. The data was too large.

While loading the data using insert statement, I didn't receive any error. BUT while accessing the data using 'Select json_data from demo' , I found that the json_data has been trimmed and shows only initial 65536 characters.

Is the a way by which we can enhance the size ?

data_type #sql #size

Answer 1

If you get the correct length using select len(json_data) from demo, then the data are not truncated.

Probably the program that is used in your test shows the partial result. In case of Management Studio, see menu, Tools, Options, Query Results, SQL Server, Results to Grid.

Answer 2

@Nandhagopal Veluchamy @TheDBLearningGuy @Bjoern Peters @Nasreen Akter @Alberto Morillo
Please help on above question

Answer 3

Hi @Harsh Gupta

As Viorel mentioned... I cannot imagine any single JSON-file (aka JSON use case) that hits 2GB of space... so there must be something wrong with the display of the result, even as SQL Server doesn't return an error after inserting...

https://learn.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver15#classic-tables

Answer 4

To my understanding 65535 is the default limit SQL Server Management Studio has to show Non-XML Data. You can increase that limit.

Answer 5

Hi @Harsh Gupta ，

Please check out this doc, it describes how to configure the max text repl size server configuration option by SSMS or T-SQL:
Configure the max text repl size Server Configuration Option

The operations of SSMS have been shown to you, it is the T-SQL:  
USE AdventureWorks2012 ;    
GO    
EXEC sp_configure 'show advanced options', 1 ;     
RECONFIGURE ;     
GO    
EXEC sp_configure 'max text repl size', -1 ;     
GO    
RECONFIGURE;     
GO  

In this example, the max text repl size is set to -1, a value of -1indaicates that there is no size limit, you can change this value according to your needs

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.