SQL Server reducing the length of the string to 8000 characters

Question

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, 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)'

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

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

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.

Answer 1

This is documented in + (String Concatenation) (Transact-SQL) - Remarks:

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.

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

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');

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');

Answer 2

In SQL Server 2017 onwards, there is CONCAT_WS function to perform concatenation easily. You can also read about CONCAT

So, instead of this:

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

We can have below:

INSERT INTO TMPRESULTS SELECT CONCAT_WS(CAST(N',' AS NVARCHAR(MAX)),'A','B','C'))

I have put sample below from SQL Server 2017 for reference:

CREATE TABLE #tempValue(BigValue NVARCHAR(MAX))

INSERT INTO #tempValue
SELECT CONCAT_WS(CAST(N',' AS NVARCHAR(MAX)),REPLICATE('A',4000),REPLICATE('B',4000),REPLICATE('C',4000))

SELECT LEN(BigValue) FROM #tempValue -- 12002

Also, CONCAT_WS is better for below reasons:

If CONCAT_WS receives arguments with all NULL values, it will return an empty string of type varchar(1).

CONCAT_WS ignores null values during concatenation, and does not add the separator between null values.