NVARCHAR(MAX) doesn't accommodate 50478 characters

Question

I'm trying to store a string of 50478 characters length in a nvarchar(max) database field. According to this link, it tells that a nvarchar(max) field can store up to 1 billion characters, but when I tried to store just 50478 characters sql truncates them and doesn't store the full string.

How to solve such a problem?

Do you think that this is just a printing problem with sql server management studio?

How do you know it stored only 50478 character? did you use `print`? — ughai, Jun 01 '15 at 09:04
What do you get when you query `SELECT LENGTH(myField) as myFieldLength FROM myTable`? It should give you 50478. — Alexander, Jun 01 '15 at 09:04
Are you doing `SELECT ..` from SSMS ? If so, note that the output will be truncated. Have a look at Tools Options under Query Results to set how many chars to display per column. — StuartLC, Jun 01 '15 at 09:06
I'm just adding it manually using sql server GUI, after that I viewed the results using Select top 1000 rows then I found the string is not fully displayed (truncated) — Ibrahim Amer, Jun 01 '15 at 09:09
Check the length using the script `SELECT TOP 1000 LEN(colname) FROM yourtable ORDER BY LEN(colname) DESC` — ughai, Jun 01 '15 at 09:10
Then it is as per @StuartLC 's comment, i.e. you can set it to 65535 (Query >> Options >> Result >> Grid) — Anthony Horne, Jun 01 '15 at 09:10
Relevant: [Display More Than 8000 Characters](http://www.sqlservercentral.com/articles/T-SQL/72129/) — Felix Pamittan, Jun 02 '15 at 03:33

score 0 · Answer 1 · answered Jun 01 '15 at 09:47

You need to ensure that the data being inserted into the field is cast as nvarchar too otherwise you will not be able to achieve what you're looking for.

Take this as an example:

Create table #temp (this nvarchar(max))

insert into #temp values (REPLICATE(cast('a' as nvarchar(max)), 50478))

Select this, Len(this) from #temp

drop table #temp

Also can be viewed on SQL Fiddle here: http://sqlfiddle.com/#!6/551f7/2/0

NVARCHAR(MAX) doesn't accommodate 50478 characters

1 Answers1