tipos - varchar max length sql server

Límites de SQL NVARCHAR y VARCHAR

Question

Todos, tengo una consulta SQL dinámica grande (inevitable). Debido al número de campos en los criterios de selección, la cadena que contiene el SQL dinámico crece a más de 4000 caracteres. Ahora, entiendo que hay un 4000 max set para NVARCHAR(MAX) , pero mirando el SQL ejecutado en Server Profiler para la sentencia

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

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

¿Qué está pasando exactamente aquí y debería simplemente convertir esta variable @SQL a VARCHAR y seguir con ella?

Gracias por tu tiempo.

PD. 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?

Answer 1

Entiendo que hay un 4000 max set para NVARCHAR(MAX)

Tu comprensión es incorrecta. nvarchar(max) puede almacenar hasta (y más de a veces) 2GB de datos (1 billón de caracteres de doble byte).

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

nvarchar [ ( n | max ) ]

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

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

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

En cuanto a las otras partes de su pregunta

El truncamiento cuando concatena depende del tipo de datos.

1. varchar(n) + varchar(n) se truncarán a 8,000 caracteres.
2. nvarchar(n) + nvarchar(n) se truncarán a 4.000 caracteres.
3. varchar(n) + nvarchar(n) se truncarán a 4.000 caracteres. nvarchar tiene mayor prioridad por lo que el resultado es nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) no se truncará (para <2GB).
5. varchar(max) + varchar(n) no se truncarán (para <2GB) y el resultado se tipeará como varchar(max) .
6. varchar(max) + nvarchar(n) no se truncarán (para <2GB) y el resultado se tipeará como nvarchar(max) .
7. nvarchar(max) + varchar(n) primero convertirá la entrada de varchar(n) a nvarchar(n) y luego realizará la concatenación. Si la longitud de la cadena varchar(n) es mayor de 4,000 caracteres, el molde será nvarchar(4000) y se producirá el truncamiento .

Tipos de datos de literales de cadena

Si usa el prefijo N y la cadena tiene <= 4,000 caracteres, se tecleará como nvarchar(n) donde n es la longitud de la cadena. Por N''Foo'' tanto, N''Foo'' se tratará como nvarchar(3) por ejemplo. Si la cadena tiene más de 4,000 caracteres, se tratará como nvarchar(max)

Si no usa el prefijo N y la cadena tiene <= 8,000 caracteres, se tecleará como varchar(n) donde n es la longitud de la cadena. Si es más largo como varchar(max)

Para ambos de los anteriores, si la longitud de la cadena es cero, 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. Ten 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);`

Devoluciones

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

Tenga en cuenta que @A encontró el truncamiento.

Cómo resolver el problema que estás experimentando

Está recibiendo truncamiento porque está concatenando dos tipos de datos no max Juntos o porque está concatenando una cadena varchar(4001 - 8000) en una cadena de caracteres 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 rango de 4001 a 8000) tengan el prefijo 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úrate de que tienes seleccionado el modo "resultados para la grilla", entonces puedes usar

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

Las opciones de SSMS le permiten establecer una longitud ilimitada para los resultados XML . El bit de processing-instruction evita problemas con caracteres como < aparecer como < .

Answer 2

De acuerdo, entonces si más adelante la cuestión es que tienes una consulta que es mayor que el tamaño permitido (lo que puede suceder si sigue creciendo) vas a tener que dividirla en fragmentos 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

Answer 3

También debes usar el texto nvarchar. eso significa que simplemente tienes que tener una "N" antes de tu secuencia masiva y eso es todo! ya no hay limitación

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

Answer 4

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