如何在 SQL Server 2005 中的一列中存储超过 8000 个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14209034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store more than 8000 characters in a column in SQL Server 2005?
提问by Rahul Tripathi
I have a table named as ABC. In this table I have a column which is defined as XYZ
我有一个名为 ABC 的表。在这张表中，我有一列被定义为XYZ
varchar(8000).
In this column I am storing the SOAP error request.
在本专栏中，我将存储 SOAP 错误请求。
My problem is that now I am getting the length of SOAP Error request more than
我的问题是，现在我得到的 SOAP 错误请求的长度超过
8000 (to be accurate it is now 16000).
I have read that the maximum length of varcharis 8000, so my question is that how can I increase the length of my column XYZ to 16000?
我读过最大长度varchar为 8000，所以我的问题是如何将我的 XYZ 列的长度增加到 16000？
回答by marc_s
If you really need more than 8000 characters, you need to use VARCHAR(MAX)which can store up to 2 GB of text:
如果确实需要超过 8000 个字符，则需要使用VARCHAR(MAX)which 最多可以存储 2 GB 的文本：
XYZ varchar(max)
This gives you up to 2 billion characters - which is Leo Tolstoj's War and Peaceabout 200 times over - should be enough for most cases!
这为您提供了多达 20 亿个字符——这是托尔斯泰的War与和平的大约 200 倍——对于大多数情况来说应该足够了！
Note: if you get a SOAP request, that most likely will be properly formatted XML - right? In that case, you could also use the XMLdatatype of SQL Server 2005and newer. It also stores up to 2 GB of data, but it stores it more efficiently than a plain varchar(max)does - and you can run XPath/XQuery against it to grab bits from it.
注意：如果您收到 SOAP 请求，那很可能是格式正确的 XML - 对吗？在这种情况下，您还可以使用XMLSQL Server 2005和更高版本的数据类型。它还可以存储多达 2 GB 的数据，但它的存储效率比普通数据更高varchar(max)- 您可以针对它运行 XPath/XQuery 以从中获取位。
So I'd recommend you use:
所以我建议你使用：
XYZ XML
回答by Raj
Use varchar(max)
用 varchar(max)
varchar(n)is variable-length, non-Unicode string data. ndefines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
varchar(n)是可变长度的非 Unicode 字符串数据。n定义字符串长度，可以是 1 到 8,000 之间的值。max 表示最大存储大小为 2^31-1 字节（2 GB）。
The storage size is the actual length of the data entered + 2 bytes.
存储大小为输入数据的实际长度+2 个字节。
回答by TomTom
Try starting by using a data type that handles long text. "Text" comes to my mind.
尝试从使用处理长文本的数据类型开始。“文本”出现在我的脑海中。
回答by Cris
use text datatype,for reference http://msdn.microsoft.com/en-us/library/ms189574(v=sql.90).aspx
使用文本数据类型，参考http://msdn.microsoft.com/en-us/library/ms189574(v=sql.90).aspx
回答by Ramesh.
--Check this example...
create table t1( i varchar(max))
create table t2( i varchar(8000))
DECLARE @String1 VARCHAR(MAX)
set @String1 = 'Hello'
declare @i int
set @i = 1
while ( @i < 50000)
begin
set @String1 = @String1 + 'world'
set @i = @i +1
end
select len(@String1)
insert into t1 select @String1 -- will succeed
insert into t2 select @String1 -- will fail
SELECT LEN(i) from t1
SELECT LEN(i) from t2
