Can't insert more than 43679 character to sql server single cell?

Question

I would like to insert 500 000 characters to sql server single cell. But, I can't insert more than 43679 characters to single cell.

I tried to create table:

create table sample1(name nvarchar(max)
create table sample2(address ntext)

But I didn't succeed

Answer 1

The issue does not come from the column type:

• nvarchar(max) can hold 2GB of data (more than 1 billion chars)
• ntext should not be used as it is deprecated and can hold 1GB of data (more than 500 million chars)

The issue seems to be when you get the data. If you are copy/pasting, just paste in a notepad to see if your data is complete.

Note that there is a known issue when you copy/paste in Grid Mode with SSMS 2008+:

• SSMS - Can not paste more than 43679 characters from a column in Grid Mode

So you could install SSMS 2005 or see if the workarounds listed here could fit your requirements:

• SSMS - Allow large text to be displayed in as a link