Limites do SQL NVARCHAR e VARCHAR

Tudo, eu tenho uma grande consulta SQL dinâmica (inevitável). Devido ao número de campos nos critérios de seleção, a string contendo o SQL dynamic está crescendo mais de 4.000 caracteres. Agora, eu entendo que há um conjunto máximo de 4000 para NVARCHAR(MAX) , mas olhando para o SQL executado no Server Profiler para a instrução

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

Parece funcionar (!?), para outra consulta que também é grande ele gera um erro que está associado com este limite de 4000 (!?), basicamente corta todo o SQL após este limite de 4000 e me deixa com um erro de syntax. Apesar disso no profiler, está mostrando essa consulta SQL dinâmica na íntegra (!?).

O que exatamente está acontecendo aqui e eu deveria estar convertendo essa variável do @SQL para VARCHAR e continuar com ela?

Obrigado pelo seu tempo.

Ps. Também seria bom poder imprimir mais de 4.000 caracteres para ver essas grandes consultas. Os seguintes são limitados a 4000

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

Existe alguma outra maneira legal?

Eu entendo que há um conjunto máximo de 4000 para NVARCHAR(MAX)

Sua compreensão está errada. nvarchar(max) pode armazenar até (e além de algumas vezes) 2GB de dados (1 bilhão de caracteres de byte duplo).

De nchar e nvarchar em livros on-line a gramática é

 nvarchar [ ( n | max ) ] 

O | caráter significa que estas são alternativas. ou seja, você especifica n ou o literal max .

Se você escolher especificar um n específico, ele deve estar entre 1 e 4.000, mas usar max define como um tipo de dados de object grande (substituição para ntext que foi descontinuado).

De fato, no SQL Server 2008, parece que, para uma variável, o limite de 2 GB pode ser excedido indefinidamente, sujeito a espaço suficiente em tempdb ( mostrado aqui ).

Quanto às outras partes da sua pergunta

O truncamento ao concatenar depende do tipo de dados.

  1. varchar(n) + varchar(n) truncará em 8.000 caracteres.
  2. nvarchar(n) + nvarchar(n) truncará em 4.000 caracteres.
  3. varchar(n) + nvarchar(n) truncará em 4.000 caracteres. nvarchar tem precedência mais alta, então o resultado é nvarchar(4,000)
  4. [n]varchar(max) + [n]varchar(max) não truncará (por <2GB).
  5. varchar(max) + varchar(n) não truncará (por <2GB) e o resultado será digitado como varchar(max) .
  6. varchar(max) + nvarchar(n) não truncará (para <2GB) e o resultado será digitado como nvarchar(max) .
  7. nvarchar(max) + varchar(n) primeiro converterá a input varchar(n) para nvarchar(n) e, em seguida, fará a concatenação. Se o comprimento da seqüência varchar(n) for maior que 4.000 caracteres, a conversão será para nvarchar(4000) e ocorrerá truncamento .

Tipos de dados de literais de string

Se você usar o prefixo N e a string tiver < = 4.000 caracteres, ele será digitado como nvarchar(n) que n é o tamanho da string. Então N'Foo' será tratado como nvarchar(3) por exemplo. Se a string tiver mais de 4.000 caracteres, ela será tratada como nvarchar(max)

Se você não usar o prefixo N e a string tiver < = 8.000 caracteres, ele será digitado como varchar(n) onde n é o tamanho da string. Se mais longo como varchar(max)

Para ambos os itens acima, se o comprimento da string for zero, então n será definido como 1.

Novos elementos de syntax.

1. A function CONCAT não ajuda aqui

 DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000); SELECT DATALENGTH(@A5000 + @A5000), DATALENGTH(CONCAT(@A5000,@A5000)); 

O acima retorna 8000 para ambos os methods de concatenação.

2. Tenha cuidado com +=

 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);` 

Retorna

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

Observe que @A encontrou truncamento.

Como resolver o problema que você está enfrentando.

Você está recebendo truncamento porque está concatenando dois tipos de dados não max juntos ou porque está concatenando uma cadeia de caracteres varchar(4001 - 8000) para uma cadeia de tipos nvarchar (mesmo nvarchar(max) ).

Para evitar o segundo problema, simplesmente certifique-se de que todos os literais de string (ou pelo menos aqueles com comprimentos no intervalo de 4001 a 8000) sejam precedidos por N

Para evitar o primeiro problema, altere a atribuição de

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

Para

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

de modo que um NVARCHAR(MAX) esteja envolvido na concatenação desde o início (como o resultado de cada concatenação também será NVARCHAR(MAX) isso se propagará)

Evitando o truncamento ao visualizar

Verifique se você selecionou o modo “resultados para a grade” e poderá usar

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

As opções do SSMS permitem que você defina um comprimento ilimitado para resultados XML . O bit de processing-instruction processamento evita problemas com caracteres como < aparecendo como < .

Ok, então se mais tarde a linha for a questão é que você tem uma consulta maior que o tamanho permitido (o que pode acontecer se continuar crescendo) você terá que dividi-la em partes e executar os valores da string. Então, digamos que você tenha um procedimento armazenado como o seguinte:

 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 

Você também pode usar o texto nvarchar. isso significa que você tem que simplesmente ter um “N” antes de sua corda maciça e é isso! nenhuma limitação mais

 DELARE @SQL NVARCHAR(MAX); SET @SQL = N'SomeMassiveString > 4000 chars...'; EXEC(@SQL); GO 
 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