Límites de SQL NVARCHAR y VARCHAR

Todos, tengo una gran (inevitable) consulta SQL dinámica. Debido al número de campos en los criterios de selección, la cadena que contiene el SQL dinámico está creciendo más de 4000 caracteres. Ahora, entiendo que hay un máximo de 4000 establecido para NVARCHAR(MAX), pero mirando el SQL ejecutado en Server Profiler para la instrucción 

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

Parece funcionar (!?), para otra consulta que también es grande arroja un error que está asociado con este límite de 4000 (!?), básicamente recorta todo el SQL después de este 4000 límite y me deja con un error de sintaxis. A pesar de esto en el generador de perfiles, está mostrando esta consulta SQL dinámica en full (!?).

¿Qué es exactamente lo que está sucediendo aquí y debería simplemente convertir esta variable @SQL a VARCHAR y seguir adelante con ella?

Gracias por su tiempo.

Ps. También sería bueno poder imprimir más de 4000 caracteres para ver estas grandes consultas. Los siguientes están limitados a 4000 

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

¿Hay alguna otra manera genial?

Author: SynozeN Technologies, 2012-09-28
Source

4 answers

Entiendo que hay un máximo de 4000 establecido para NVARCHAR(MAX)

Tu entendimiento está equivocado. nvarchar(max) puede almacenar hasta (y más allá a veces) 2GB de datos (1 billón de caracteres de byte doble).

De nchar y nvarchar en los Libros en línea la gramática es 

nvarchar [ ( n | max ) ]

El carácter | significa que estas son alternativas. es decir, se especifica bien n o el literal max.

Si elige especificar un n específico, entonces esto debe entre 1 y 4.000 pero usando max lo define como un tipo de datos de objeto grande (reemplazo de ntext que está en desuso).

De hecho, en SQL Server 2008 parece que para una variable el límite de 2 GB se puede exceder indefinidamente sujeto a espacio suficiente en tempdb (Se muestra aquí )

Con respecto a las otras partes de su pregunta

El truncamiento al concatenar depende del tipo de datos.

  1. varchar(n) + varchar(n) se truncará a 8.000 caracter.
  2. nvarchar(n) + nvarchar(n) se truncará a 4.000 caracteres.
  3. varchar(n) + nvarchar(n) se truncará a 4.000 caracteres. nvarchar tiene mayor precedencia por lo que el resultado es nvarchar(4,000)
  4. [n]varchar(max) + [n]varchar(max) no se trunca (para
  5. varchar(max) + varchar(n) no se trunca (para varchar(max).
  6. varchar(max) + nvarchar(n) no se trunca (para nvarchar(max).
  7. nvarchar(max) + varchar(n) primero convertirá la entrada varchar(n) a nvarchar(n) y luego hacer la concatenación. Si la longitud de la cadena varchar(n) es mayor que 4.000 caracteres, el cast será nvarchar(4000) y se producirá el truncamiento.

Tipos de datos de literales de cadena

Si usa el prefijo N y la cadena tiene nvarchar(n) donde n es la longitud de la cadena. Así que N'Foo' se tratará como nvarchar(3) por ejemplo. Si la cadena es más larga que 4.000 caracteres se tratará como nvarchar(max)

Si no usa el prefijo N y la cadena tiene varchar(n) donde n es la longitud de la cadena. Si es más largo que varchar(max)

Para ambos de los anteriores si la longitud de la cadena es cero entonces n se establece en 1.

Nuevos elementos de sintaxis.

1. La función CONCAT no ayuda aquí 

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

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

Lo anterior devuelve 8000 para ambos métodos de concatenación.

2. Be cuidado 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);`

Devuelve 

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

Note que @A encontró truncamiento.

Cómo resolver el problema que estás experimentando.

Está obteniendo truncamiento ya sea porque está concatenando dos tipos de datos no max juntos o porque está concatenando una cadena varchar(4001 - 8000) a una cadena escrita nvarchar (incluso nvarchar(max)).

Para evitar el segundo problema, simplemente asegúrese de que todos los literales de cadena (o al menos aquellos con longitudes en el 4001 - 8000 range) están precedidos por N.

Para evitar el primer problema, cambie la asignación de 

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

A

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

De modo que un NVARCHAR(MAX) está involucrado en la concatenación desde el principio (como el resultado de cada concatenación también será NVARCHAR(MAX) esto se propagará)

Evitar el truncamiento al ver

Asegúrese de que tiene seleccionado el modo" resultados a cuadrícula", entonces puede usar

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

Las opciones SSMS le permiten establecer una longitud ilimitada para XML resultados. El bit processing-instruction evita problemas con caracteres como < que aparecen como &lt;.

 212
Author: Martin Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:10:31

Bien, así que si más adelante en la línea el problema es que tiene una consulta que es mayor que el tamaño permitido (que puede suceder si sigue creciendo) vas a tener que dividirla en trozos y ejecutar los valores de cadena. Entonces, digamos que tiene un procedimiento almacenado como el siguiente:

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
Author: Mike Perrenoud,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-09-28 12:41:51

Usted también usa nvarchar texto. eso significa que tienes que simplemente tenía una " N " antes de su cuerda masiva y eso es todo! ya no hay limitación

DELARE @SQL NVARCHAR(MAX);
SET @SQL = N'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO
 2
Author: Max,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-18 07:32:35 
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
Author: Heta77,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-03-23 09:35:54