Size limit problem with ntext in Sql Server CE 3.5

Question

I am experiencing some strange behavior with SQL Server CE 3.5 SP2.

I have a table with 2 columns; one of type int named ID, which is the primary key, and one of type ntext named 'Value'. The 'Value' column is supposed to contain rather long string values. However, when I try to store a string that is longer than 4000 characters, the value turns into an empty string!

I am using the Visual Studio 2010 server explorer to do this.

What's going on? I thought this 4000 character limit was for nvarchar and that ntext had a 2GB limit. Am I forgetting something or are these limits different for SQL Server CE? MSDN is not very clear on this.

Answer 1

Alright, after trying a lot of things and reading many obscure posts on the subject, it turned out to be not a sql server CE problem at all, but an issue with Visual Studio.

There is a setting under Options->Database Tools->Query results, that specifies the maximum numbers of characters retrieved from a query. What happened was that after the string was entered in the Server Explorer table editor, it was actually persisted in SQL Server CE but visual studio could not display it due to the aforementioned setting.

Answer 2

According to the documentation the limit is 536,870,911 characters:

http://msdn.microsoft.com/en-us/library/ms172424(v=SQL.100).aspx

This seems to explain what you're seeing:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/9fe8e826-7c20-466c-8140-4d3b0649ac09

Answer 3

The data type NTEXT SQL Server CE can actually store up to 536870911 characters. This represents a physical space of 1073741822 bytes or about 1 gigabyte, or half of 2Gbytes that SQL Server would store. But this ability is not so much: Observe other factors that limit this ability. First, the data file can store a maximum of slightly less than 4 gigabytes, given the reservation of space needed will change pages. A single record with a quarter of that size, so it will be quite time consuming to be loaded, and may appear to be blank spaces (not null) when in fact it is not. Second, observe some caution with commands to select the data, which can inadvertently convert one type to another. As an example, can occur converting NTEXT to NVARCHAR, for example, when using an ALIAS for the selected fields. E values ​​above the capacity of the NVARCHAR in fields converted, may appear as blank spaces too.