[解決済み] SQLのNVARCHARとVARCHARの限界値

2022-12-23 08:17:15

質問

大きな（避けられない）動的SQLクエリを持っています。選択基準のフィールドの数が多いため、動的 SQL を含む文字列が 4000 文字以上に増えています。現在、私は、最大 4000 文字が NVARCHAR(MAX) に 4000 の最大値が設定されていることは理解していますが、Server Profiler で実行された SQL を見てみると、ステートメントは

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

動作しているように見えますが(!?)、同じく大きな別のクエリでは、この4000の制限(!?)に関連したエラーが投げられます。基本的に、この4000の制限以降のすべてのSQLが切り捨てられ、構文エラーが残されます。プロファイラでは、この動的SQLクエリは full (!?).

ここで一体何が起こっているのでしょうか、そして私はこの@SQL変数をVARCHARに変換して取り掛かればよいのでしょうか。

お時間をいただきありがとうございました。

Ps. また、これらの大きなクエリを見るために、4000文字以上の印刷ができるといいのですが。以下は4000に制限されています。

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

他にいい方法はないのでしょうか？

どのように解決するのですか？

<ブロッククオート

の最大値が4000に設定されていることは理解しています。 NVARCHAR(MAX)

あなたの理解は間違っています。 nvarchar(max) は最大 2GB (場合によってはそれ以上) のデータ (10億ダブルバイト文字) を格納することができます。

から nchar および nvarchar の文法は、Books online では

nvarchar [ ( n | max ) ]

は | の文字は、これらが選択肢であることを意味します。 のどちらかを n またはリテラル max .

もし、特定の n を指定する場合、これは 1 から 4,000 の間でなければなりません。 max を使うとラージオブジェクトのデータ型として定義されます。 ntext の置き換え）。

実際、SQL Server 2008 では変数において十分なスペースがあれば、2GB の制限を無制限に超えることができるようです。 tempdb ( ここに表示されます )

ご質問の他の部分について

連結時の切り捨てはデータ型に依存します。

varchar(n) + varchar(n) は8,000文字で切り捨てられます。
nvarchar(n) + nvarchar(n) は4,000文字で切り捨てられます。
varchar(n) + nvarchar(n) は4,000文字で切り捨てられます。 nvarchar の方が優先順位が高いので、結果は nvarchar(4,000)
[n]varchar(max) + [n]varchar(max) は切り捨てられません(< 2GBの場合)。
varchar(max) + varchar(n) は切り捨てられず（2GBの場合）、結果は次のように入力されます。 varchar(max) .
varchar(max) + nvarchar(n) は切り捨てられず（2GBの場合）、結果は次のように入力されます。 nvarchar(max) .
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)

上記のどちらも、文字列の長さがゼロの場合は n は1に設定されます。

新しい構文要素です。

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 は切り捨てに遭遇したことに注意してください。

発生している問題を解決する方法。

切り捨てが発生するのは、2つの非対称な max データ型を連結しているか、または varchar(4001 - 8000) の文字列を nvarchar のような型付けされた文字列（たとえ nvarchar(max) ).

2番目の問題を避けるには、すべての文字列リテラル（少なくとも4001から8000の範囲の長さのもの）の前に、単純に N .

最初の問題を回避するために、割り当てを

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

への

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

というように NVARCHAR(MAX) は最初から連結に関与しています（各連想の結果もまた NVARCHAR(MAX) となり、伝播していきます)

表示時の切り詰めを回避する

グリッドに結果を表示するモードが選択されていることを確認してください。

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

SSMSのオプションで、長さを無制限に設定できるのは XML の結果を表示します。そのため processing-instruction のような文字の問題を回避することができます。 < のように表示されます。 < .