100 votos

SQL de tipo NVARCHAR y VARCHAR Límites

Todos, tengo un gran (inevitable) dinámica de la consulta SQL. Debido a que el número de campos en los criterios de selección de la cadena que contiene el SQL dinámico está creciendo más de 4000 caracteres. Ahora, yo entiendo que no es de 4000 max para NVARCHAR(MAX), pero mirando en la ejecuta el SQL Server Profiler para la declaración de

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

Parece que funciona(!?), para la otra pregunta que también es grande se tira un error que es ascociated con este 4000 límite(!?), básicamente todos los adornos de la SQL después de este límite de 4000 y me deja con un error de sintaxis. Pesar esta en el analizador está mostrando esta dinámica de consultas SQL en pleno(!?).

Por qué es exactamente lo que sucede aquí y debo ser la conversión de este @SQL variable de tipo VARCHAR y conseguir con ello?

Gracias por su tiempo.

Ps. También sería agradable ser capaz de imprimir más de 4000 caracteres a buscar a estas grandes preguntas. Los siguientes son limitado a 4000

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

es allí cualquier otro fresco manera?

235voto

Martin Smith Puntos 174101

Yo entiendo que no es de 4000 max para NVARCHAR(MAX)

Su comprensión está mal. nvarchar(max) puede almacenar hasta (y más allá a veces) 2 GB de datos (de 1 mil millones de caracteres de doble byte).

De nchar y nvarchar en los Libros en pantalla de la gramática es

nvarchar [ ( n | max ) ]

El | carácter significa que estas son las alternativas. es decir, especifique cualquiera de las n o el literal max.

Si usted elige para especificar un determinado n , a continuación, este debe estar entre 1 y 4.000 pero el uso de max lo define como un objeto de gran tamaño de tipo de datos (sustituto de la ntext que está en desuso).

De hecho, en SQL Server 2008 parece que para una variable el límite de 2 gb puede ser superado de forma indefinida, sujeto a espacio suficiente en tempdb (Mostrado aquí)

Con respecto a las otras partes de la pregunta

El truncamiento al concatenar depende del tipo de datos.

  1. varchar(n) + varchar(n) truncará en 8.000 caracteres.
  2. nvarchar(n) + nvarchar(n) truncará a 4.000 caracteres.
  3. varchar(n) + nvarchar(n) truncará 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 truncar.
  5. varchar(max) + nvarchar(n) no truncar y el resultado será de tipo nvarchar(max).
  6. nvarchar(max) + varchar(n) primero se convierte el varchar(n) entrada nvarchar(n) y, a continuación, hacer la concatenación. Si la longitud de la varchar(n) cadena es mayor que 4.000 caracteres el reparto se a nvarchar(4000) y se producirá un truncamiento.

Tipos de datos de los literales de cadena

Si utiliza el N prefijo y la cadena es <= 4.000 caracteres de largo va a ser de tipo nvarchar(n) donde n es la longitud de la cadena. Por lo N'Foo' será tratada como nvarchar(3) , por ejemplo. Si la cadena es de más de 4.000 caracteres que serán tratados como nvarchar(max)

Si usted no utiliza el N prefijo y la cadena es <= 8.000 caracteres de largo va a ser de tipo varchar(n) donde n es la longitud de la cadena. Si no como varchar(max)

Tanto de los de arriba, si la longitud de la cadena es cero, a continuación, n está configurado para 1.

Cómo resolver el problema que está experimentando.

Usted está recibiendo la concatenación ya sea porque son la concatenación de dos no max tipos de datos juntos o porque usted es la concatenación de una varchar(4001 - 8000) cadena a un nvarchar de tipo string (aun 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 gama) están precedidos por N.

Para evitar el primer problema de cambiar la asignación de

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

A

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

de modo que un NVARCHAR(MAX) está involucrado en la concatenación desde el principio (como el resultado de la concatenación también estarán NVARCHAR(MAX) este se propaga)

Evitar el truncamiento al ver

Asegúrese de que tiene "resultados a la cuadrícula" en el modo seleccionado, a continuación, puede utilizar

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

El SSMS opciones le permiten establecer una longitud ilimitada para XML de resultados. El processing-instruction poco evita problemas con caracteres tales como < mostrando como &lt;.

6voto

Michael Perrenoud Puntos 37869

Bien, así que si más adelante en la línea de la cuestión es que usted tiene una consulta que es más grande que el tamaño permitido (lo cual puede suceder si se sigue creciendo) vas a tener que romper en trozos y ejecutar la cadena de valores. Así que, digamos que usted 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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X