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

Harsh Gupta 26 Reputation points
2022-11-15T07:46:07.963+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,339 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points
    2022-11-15T08:21:14.053+00:00

    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.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Harsh Gupta 26 Reputation points
    2022-11-15T07:48:42.047+00:00
    0 comments No comments

  2. Bjoern Peters 8,856 Reputation points
    2022-11-15T11:16:38.217+00:00

    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

    0 comments No comments

  3. Alberto Morillo 33,426 Reputation points MVP
    2022-11-16T01:16:21.12+00:00

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

    260732-image.png

    260684-image.png

    0 comments No comments

  4. YufeiShao-msft 7,081 Reputation points
    2022-11-16T02:23:39.537+00:00

    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.

    0 comments No comments