Limiti di NVARCHAR e VARCHAR di SQL

Comprendo che esiste un set massimo di 4000 per NVARCHAR(MAX)

La tua comprensione è sbagliata. nvarchar(max) può memorizzare fino a (e oltre a volte) 2 GB di dati (1 miliardo di caratteri a doppio byte).

Da nchar e nvarchar in Libri online la grammatica è 

nvarchar [ ( n | max ) ]

Il carattere | indica che si tratta di alternative. ad esempio, si specifica entrambin o il max letterale.

Se si sceglie di specificare un n specifico, questo deve essere compreso tra 1 e 4.000, ma usando max lo definisce come un tipo di dati di oggetto di grandi dimensioni (sostitutivo di ntext che è deprecato).

Infatti in SQL Server 2008 sembra che per un variabile il limite di 2 GB possa essere superato indefinitamente soggetto a spazio sufficiente in tempdb ( Mostrato qui )

Riguardo le altre parti della tua domanda

Il troncamento quando la concatenazione dipende dal tipo di dati.

1. varchar(n) + varchar(n) troncherà a 8.000 caratteri. 
2. nvarchar(n) + nvarchar(n) troncherà a 4000 caratteri. 
3. varchar(n) + nvarchar(n) troncherà a 4000 caratteri. nvarchar ha precedenza più alta quindi il risultato è nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) non troncerà (per <2 GB). 
5. varchar(max) + varchar(n) non troncerà (per <2 GB) e il risultato verrà digitato come varchar(max).
6. varchar(max) + nvarchar(n) non troncerà (per <2 GB) e il risultato verrà digitato come nvarchar(max). 
7. nvarchar(max) + varchar(n) convertirà dapprima l'input varchar(n) in nvarchar(n) e quindi eseguirà la concatenazione. Se la lunghezza della stringa varchar(n) è maggiore di 4.000 caratteri, il cast sarà in nvarchar(4000) e si verificherà il troncamento.

Tipi di dati di stringhe letterali

Se si utilizza il prefisso N e la stringa è <= 4.000 caratteri, verrà digitata come nvarchar(n) dove n è la lunghezza della stringa. Quindi N'Foo' verrà trattato come nvarchar(3) ad esempio. Se la stringa è più lunga di 4.000 caratteri sarà trattata come nvarchar(max)

Se non si utilizza il prefisso N e la stringa è <= 8.000 caratteri, verrà digitata come varchar(n) dove n è la lunghezza della stringa. Se più lungo come varchar(max)

Per entrambi i precedenti se la lunghezza della stringa è zero, n è impostato su 1.

Elementi di sintassi più recenti.

1. La funzione CONCAT non aiuta qui

DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000);

SELECT DATALENGTH(@A5000 + @A5000), 
       DATALENGTH(CONCAT(@A5000,@A5000));

Quanto sopra restituisce 8000 per entrambi i metodi di concatenazione.

2. Fai attenzione con +=

DECLARE @A VARCHAR(MAX) = '';

SET @A+= REPLICATE('A',5000) + REPLICATE('A',5000)

DECLARE @B VARCHAR(MAX) = '';

SET @B = @B + REPLICATE('A',5000) + REPLICATE('A',5000)


SELECT DATALENGTH(@A), 
       DATALENGTH(@B);`

Ritorna

-------------------- --------------------
8000                 10000

Si noti che @A ha rilevato il troncamento.

Come risolvere il problema che stai riscontrando.

Si sta ottenendo il troncamento o perché si concatenano due tipi di dati non max o perché si concatena una stringa varchar(4001 - 8000) a una stringa tipizzata nvarchar (anche nvarchar(max)).

Per evitare il secondo problema è sufficiente assicurarsi che tutti i valori letterali stringa (o almeno quelli con lunghezze nell'intervallo 4001 - 8000) siano preceduti da N. 

Per evitare il primo problema cambia il compito da 

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'Foo' + 'Bar' + ...;

A

DECLARE @SQL NVARCHAR(MAX) = ''; 
SET @SQL = @SQL + N'Foo' + N'Bar'

in modo che un NVARCHAR(MAX) sia coinvolto nella concatenazione dall'inizio (poiché il risultato di ogni concatenazione sarà anche NVARCHAR(MAX) questo si propagherà)

Evitare il troncamento durante la visualizzazione

Assicurati di aver selezionato la modalità "risultati in griglia" che puoi utilizzare

select @SQL as [processing-instruction(x)] FOR XML PATH 

Le opzioni SSMS ti consentono di impostare una lunghezza illimitata per i risultati XML. Il processing-instruction bit evita problemi con caratteri come < che appaiono come <.