risposta-alla-domanda-sullo-sviluppo-web-bd.com

Limiti di NVARCHAR e VARCHAR di SQL

Tutto, ho una grande query SQL dinamica (inevitabile). A causa del numero di campi nei criteri di selezione, la stringa contenente l'SQL dinamico sta crescendo di oltre 4000 caratteri. Ora, capisco che c'è un set di 4000 max per NVARCHAR(MAX), ma guardando l'SQL eseguito in Server Profiler per l'istruzione 

DELARE @SQL NVARCHAR(MAX);
SET @SQL = 'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO

Sembra funzionare (!?), Per un'altra query che è anche grande genera un errore associato a questo limite 4000 (!?), in pratica riduce tutto l'SQL dopo questo limite di 4000 e mi lascia con un errore di sintassi. Nonostante ciò nel profiler, mostra questa query SQL dinamica in full (!?). 

Cosa sta succedendo esattamente qui e dovrei semplicemente convertire questa variabile @SQL in VARCHAR e andare avanti con essa?

Grazie per il tuo tempo.

Ps. Sarebbe anche bello poter stampare più di 4.000 caratteri per esaminare queste grandi domande. I seguenti sono limitati a 4000 

SELECT CONVERT(XML, @SQL);
PRINT(@SQL);

c'è qualche altro modo interessante?

92
MoonKnight

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 &lt;.

218
Martin Smith

Va bene, quindi se più avanti sulla linea il problema è che hai una query che è maggiore della dimensione consentita (che può accadere se continua a crescere) dovrai romperla in blocchi ed eseguire il valori stringa. Quindi, diciamo che hai una stored procedure come la seguente:

CREATE PROCEDURE ExecuteMyHugeQuery
    @SQL VARCHAR(MAX) -- 2GB size limit as stated by Martin Smith
AS
BEGIN
    -- Now, if the length is greater than some arbitrary value
    -- Let's say 2000 for this example
    -- Let's chunk it
    -- Let's also assume we won't allow anything larger than 8000 total
    DECLARE @len INT
    SELECT @len = LEN(@SQL)

    IF (@len > 8000)
    BEGIN
        RAISERROR ('The query cannot be larger than 8000 characters total.',
                   16,
                   1);
    END

    -- Let's declare our possible chunks
    DECLARE @Chunk1 VARCHAR(2000),
            @Chunk2 VARCHAR(2000),
            @Chunk3 VARCHAR(2000),
            @Chunk4 VARCHAR(2000)

    SELECT @Chunk1 = '',
           @Chunk2 = '',
           @Chunk3 = '',
           @Chunk4 = ''

    IF (@len > 2000)
    BEGIN
        -- Let's set the right chunks
        -- We already know we need two chunks so let's set the first
        SELECT @Chunk1 = SUBSTRING(@SQL, 1, 2000)

        -- Let's see if we need three chunks
        IF (@len > 4000)
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, 2000)

            -- Let's see if we need four chunks
            IF (@len > 6000)
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, 2000)
                SELECT @Chunk4 = SUBSTRING(@SQL, 6001, (@len - 6001))
            END
              ELSE
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, (@len - 4001))
            END
        END
          ELSE
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, (@len - 2001))
        END
    END

    -- Alright, now that we've broken it down, let's execute it
    EXEC (@Chunk1 + @Chunk2 + @Chunk3 + @Chunk4)
END
6
Mike Perrenoud

La risposta accettata mi ha aiutato ma sono rimasto incastrato mentre facevo concatenazione di varchars che coinvolgevano dichiarazioni di casi. So che la domanda dell'OP non coinvolge le dichiarazioni dei casi, ma ho pensato che sarebbe stato utile postare qui per altri come me che sono finiti qui mentre faticavo a costruire lunghe istruzioni SQL dinamiche che coinvolgessero dichiarazioni di casi.

Quando si utilizzano le istruzioni case con concatenazione di stringhe, le regole menzionate nella risposta accettata si applicano indipendentemente a ciascuna sezione dell'istruzione case.

declare @l_sql varchar(max) = ''

set @l_sql = @l_sql +
case when 1=1 then
    --without this correction the result is truncated
    --CONVERT(VARCHAR(MAX), '')
 +REPLICATE('1', 8000)
 +REPLICATE('1', 8000)
end

print len(@l_sql)
1
Joe
declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars
0
Heta77