SQL NVARCHAR 및 VARCHAR 제한

SQL NVARCHAR 및 VARCHAR 제한

---

모두, 큰 (피할 수없는) 동적 SQL 쿼리가 있습니다. 선택 기준의 필드 수로 인해 동적 SQL을 포함하는 문자열이 4000 자 이상 증가합니다. 이제에는 4000 max가 설정되어 NVARCHAR(MAX)있지만 Server Profiler에서 실행 된 SQL을보고

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

작동하는 것 같습니다 (!?), 또한 큰 다른 쿼리의 경우이 4000 제한 (!?)과 관련된 오류가 발생하며 기본적 으로이 4000 제한 이후의 모든 SQL을 자르고 구문 오류가 발생합니다. 프로파일 러에서는 이에도 불구하고이 동적 SQL 쿼리를 전체 (!?)로 표시합니다.

여기서 정확히 무슨 일이 일어나고 있으며이 @SQL 변수를 VARCHAR로 변환하고 계속해야합니까?

시간 내 줘서 고마워.

추신. 이러한 큰 쿼리를보기 위해 4000 자 이상을 인쇄 할 수 있으면 좋을 것입니다. 다음은 4000 개로 제한됩니다.

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

다른 멋진 방법이 있습니까?

---

나는 4000 최대 세트가 있음을 이해합니다. NVARCHAR(MAX)

이해가 잘못되었습니다. nvarchar(max)최대 (때로는 그 이상) 2GB의 데이터 (10 억 2 바이트 문자)를 저장할 수 있습니다.

에서 NCHAR 및 NVARCHAR 책 온라인 문법이다

nvarchar [ ( n | max ) ]

|문자 수단이 대안이다. 즉 , n 또는 리터럴 을 지정 합니다max .

특정을 지정하도록 선택하는 경우 n1에서 4,000 사이 여야하지만 using을 사용 max하면이를 대형 개체 데이터 유형으로 정의합니다 (대체 ntext가 더 이상 사용되지 않음).

실제로 SQL Server 2008에서는 변수의 경우 2GB 제한이 tempdb( 여기에 표시됨 )의 충분한 공간에 따라 무기한 초과 될 수 있습니다.

질문의 다른 부분에 관해서

연결시 잘림은 데이터 유형에 따라 다릅니다.

1. varchar(n) + varchar(n) 8,000 자에서 잘립니다.
2. nvarchar(n) + nvarchar(n) 4,000 자에서 잘립니다.
3. varchar(n) + nvarchar(n)4,000 자에서 잘립니다. nvarchar우선 순위가 높으므로 결과는nvarchar(4,000)
4. [n]varchar(max)+ [n]varchar(max)는 잘리지 않습니다 (2GB 미만).
5. varchar(max)+ varchar(n)는 잘리지 않으며 (2GB 미만) 결과는 varchar(max).
6. varchar(max)+ nvarchar(n)는 잘리지 않으며 (2GB 미만) 결과는 nvarchar(max).
7. nvarchar(max)+ varchar(n)는 먼저 varchar(n)입력을 로 변환 nvarchar(n)한 다음 연결을 수행합니다. 의 길이 경우 varchar(n)문자열보다 큰 4,000 자 캐스트에있을 것입니다 nvarchar(4000)및 절단이 발생합니다 .

문자열 리터럴의 데이터 유형

N접두사 를 사용하고 문자열 길이가 <= 4,000자인 경우 문자열 길이가 nvarchar(n)어디에있는 것처럼 입력됩니다 n. 그래서 예를 들어 N'Foo'취급됩니다 nvarchar(3). 문자열이 4,000 자보다 길면 다음과 같이 처리됩니다.nvarchar(max)

N접두사를 사용하지 않고 문자열 길이가 <= 8,000자인 경우 문자열 길이가 varchar(n)어디에있는 것처럼 입력됩니다 n. 더 긴 경우varchar(max)

위의 두 경우 모두 문자열 길이가 0이면 n1로 설정됩니다.

최신 구문 요소.

1. 이 CONCAT기능은 여기서 도움이되지 않습니다.

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

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

위의 두 연결 방법에 대해 8000을 반환합니다.

2. 조심하세요+=

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

보고

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

@A잘림 이 발생했습니다.

발생한 문제를 해결하는 방법.

두 개의 비 max데이터 유형을 함께 연결하거나 varchar(4001 - 8000)문자열을 nvarchar유형이 지정된 문자열 (심지어 nvarchar(max))에 연결하기 때문에 잘림이 발생합니다 .

두 번째 문제를 방지하려면 모든 문자열 리터럴 (또는 최소한 4001-8000 범위의 길이를 가진 문자열) 앞에 N.

첫 번째 문제를 방지하려면 다음에서 할당을 변경하십시오.

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

에

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

so that an NVARCHAR(MAX) is involved in the concatenation from the beginning (as the result of each concatenation will also be NVARCHAR(MAX) this will propagate)

Avoiding truncation when viewing

Make sure you have "results to grid" mode selected then you can use

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

The SSMS options allow you to set unlimited length for XML results. The processing-instruction bit avoids issues with characters such as < showing up as <.

---

Okay, so if later on down the line the issue is that you have a query that's greater than the allowable size (which may happen if it keeps growing) you're going to have to break it into chunks and execute the string values. So, let's say you have a stored procedure like the following:

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

---

You mus use nvarchar text too. that's mean you have to simply had a "N" before your massive string and that's it! no limitation anymore

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

---

The accepted answer helped me but I got tripped up while doing concatenation of varchars involving case statements. I know the OP's question does not involve case statements but I thought this would be helpful to post here for others like me who ended up here while struggling to build long dynamic SQL statements involving case statements.

When using case statements with string concatenation the rules mentioned in the accepted answer apply to each section of the case statement independently.

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)

---

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

참고URL : https://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits