SQL Server 将字符串的长度减少到 8000 个字符 [英] SQL Server reducing the length of the string to 8000 characters

问题描述

我试图在列数据类型为 NTEXT 的表中插入数据.理想情况下，它应该存储超过 8000 个字符，但在我的情况下，它将它减少到 8000 个字符.

I am trying to insert data in a table with column datatype as NTEXT. Ideally it should store more than 8000 characters, but the in my case it is reducing it to 8000 characters.

我正在程序中在运行时进行插入查询.以下是该过程正在生成的示例查询.

I am making the Insert Query at runtime in Procedure. Below is the sample query that procedure is making.

INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C')

A、B、C 等是示例数据，实际数据将在运行时识别，实际内容超过 8000 个字符.用于存储值的变量也定义为 'NVARCHAR(MAX)'

A,B,C, etc. are sample data and actual data will be identified at runtime with actual content crossing 8000 characters. Also the variable used to store the value are defined as 'NVARCHAR(MAX)'

但是，当我尝试以下查询时，它确实在表中插入了 8000 多个字符

However, when I try following query it does insert more than 8000 character in the table

INSERT INTO TMPRESULTS SELECT ('ABCdddd................')

我认为当我试图用+"号连接数据时，sql server 正在将长度减少到 8000.我不能使用 CONCAT 因为数据将超过 256 列/参数.

I presume while I am trying to concat the data with '+' sign, sql server is reducing the length to 8000. I can't use CONCAT as data will be more than 256 columns/arguments.

知道为什么要这样做吗?另外，如果有人可以帮助提供一些替代解决方案，因为我将不得不在运行时进行插入查询.

Any idea, why it is doing so? Also, if someone can help with some alternate solution as I will have to make insert query at runtime.

推荐答案

这在 + (String Concatenation) (Transact-SQL) - 备注:

如果串接的结果超过了限制8,000 字节，结果被截断.但是，如果至少其中一项串接的字符串是大值类型，不会发生截断.

If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

对于 varchar 8,000 字节将是 8,000 个字符，对于 nvarchar 4,000.

For a varchar 8,000 bytes would be 8,000 characters, and for a nvarchar 4,000.

查询中的所有文字字符串 INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C') 都是非大值类型(事实上，它们都是一个 varchar(1)).如果您 CONVERT/CAST 其中之一为 varchar(MAX) 这将解决问题:

All your literal strings in the query INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C') are non large value types (In fact, they are all a varchar(1)). If you CONVERT/CAST one of them to a varchar(MAX) this would solve the problem:

INSERT INTO TMPRESULTS
SELECT (CONVERT(varchar(MAX),'A') + ',' + 'B' + ',' + 'C');

如果您想要 nvarchar，请确保您也将文字字符串声明为 nvarchar:

if you want an nvarchar, make sure you declare your literal strings as a nvarchar too:

INSERT INTO TMPRESULTS
SELECT (CONVERT(nvarchar(MAX),N'A') + N',' + N'B' + N',' + N'C');

这篇关于SQL Server 将字符串的长度减少到 8000 个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！