我在使用 SQL Server CE 3.5 SP2 时遇到一些奇怪的行为。
我有一个有 2 列的表；一种名为 ID 的 int 类型（主键），另一种为 ntext 类型，名为“Value”。 “值”列应该包含相当长的字符串值。但是，当我尝试存储超过 4000 个字符的字符串时，该值会变成空字符串！
我正在使用 Visual Studio 2010 服务器资源管理器来执行此操作。
这是怎么回事？我认为这个 4000 个字符的限制是针对 nvarchar 的，而 ntext 有 2GB 的限制。我是否忘记了什么，或者这些限制对于 SQL Server CE 是否有所不同？ MSDN对此不是很清楚。
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.
发布评论
评论（3）
根据文档，限制为 536,870,911 个字符：
http ://msdn.microsoft.com/en-us/library/ms172424(v=SQL.100).aspx
这似乎解释了您所看到的内容：
http://social.msdn.microsoft.com/Forums/en-US /sqlce/线程/9fe8e826-7c20-466c-8140-4d3b0649ac09
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
好吧，在尝试了很多方法并阅读了许多关于该主题的晦涩帖子之后，事实证明这根本不是 sql server CE 问题，而是 Visual Studio 的问题。
选项 -> 数据库工具 -> 查询结果下有一个设置，用于指定从查询中检索的最大字符数。发生的情况是，在服务器资源管理器表编辑器中输入字符串后，它实际上保留在 SQL Server CE 中，但由于上述设置，Visual Studio 无法显示它。
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.
NTEXT SQL Server CE 数据类型实际上最多可以存储 536870911 个字符。
这表示 1073741822 字节或大约 1 GB 的物理空间，或者 SQL Server 将存储的 2G 字节的一半。
但这种能力并没有那么重要：观察限制这种能力的其他因素。
首先，数据文件最多可以存储略小于 4 GB 的空间，因为预留的空间会更改页面。单个记录的大小是该大小的四分之一，因此加载会非常耗时，并且可能看起来是空格（非空），而实际上并非如此。
其次，请谨慎使用选择数据的命令，这可能会无意中将一种类型转换为另一种类型。
例如，当对选定字段使用 ALIAS 时，可能会发生将 NTEXT 转换为 NVARCHAR 的情况。转换字段中高于 NVARCHAR 容量的 E 值也可能显示为空格。
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.