INSERT や UPDATE でデータの切り捨てエラー 8152 が発生することがある
事象
INSERT や UPDATE を実行する際に、対象の列のデータ長よりも長いデータを INSERT/UPDATE しようとすると、エラー 8152が発生します。
| エラー 8152 文字列データまたはバイナリ データが切り捨てられます。 |
SQL Server では暗黙的にデータを変換してエラーを発生させないようにしますが、長いデータをINSERTしようとした場合には ANSI_WARNINGS の設定に従いエラーとなります。例1のように明らかに長いデータを INSERT しようとしてエラーとなるのは分かりやすいパターンです。例2のように、INSERT INTO SELECT やさらにテーブル結合を行っているような場合には、実行プランによってエラーが発生する場合と発生しない場合があるため、注意が必要です。
このブログでは、例2について説明したいと思います。
例1
-- テーブル CREATE TABLE table01(c1 varchar(10)) GO -- varchar(20) から varchar(10) へ暗黙的に変換され、データの切り捨てが行われないため正常終了する DECLARE @c1 varchar(20) SET @c1 = '1234567890' INSERT INTO table01 VALUES(@c1) -- varchar(20) から varchar(10) へ暗黙的に変換されるが、データの切り捨てが発生するため、エラーとなる DECLARE @c1 varchar(20) SET @c1 = '12345678901234567890' INSERT INTO table01 VALUES(@c1) |
例2
-- テーブル CREATE TABLE table01(c1 varchar(10)) GO CREATE TABLE table02(c1 varchar(20), c2 int) GO CREATE TABLE table03(c1 varchar(10)) GO -- エラーになる場合とならない場合がある INSERT INTO table01(C1) SELECT table02.C1 FROM table02 INNER JOIN table03 ON table02.C1 = table03.C1 WHERE table02.C2 = 1 |
原因
生成される実行プランによって、データの切り捨てが発生する場合としない場合があり、データの切り捨てが発生する場合には、ANSI_WARNINGS の設定に従いエラーが発生します。
例2のように、INSERT INTO SELECT での INSERTやテーブル結合を行っている場合には、データ量等によって実行プランが変わる場合があり、 プランによって、次のようにエラーが発生するパターンとしないパターンが出てきます。a) のようなプランになった場合には、エラーが発生します。
a) エラーが発生するパターン
table02で、table02.C2 = 1の条件に合致するデータをまずは取得する。この中には、varchar(20) のデータが含まれている。
このデータに対して、varchar(20) から varchar(10) へ暗黙的な変換をするため、エラーとなる。
b) エラーが発生しないパターン
table02 と table03 で、TABLE02.ID = TABLE03.ID での INNER JOIN をまずは実行する。この時点で varchar(20) のデータは含まれなくなる。
このデータに対して、varchar(20) から varchar(10) へ暗黙的な変換をするが、切り捨てが発生するデータはないため、エラーは発生しない。
この動作は、SQL Server にて単純にクエリに書かれた順序で条件評価やテーブルへのアクセスを実行するのではなく、結合順序やフィルターをかけるタイミングの有効性などを最適化して実行する動作となっており、上述の様な実行プランの変更が発生することは、SQL Server にて想定された動作となります。
対処
実行プランの変化により、暗黙的な変換でデータの切り捨てエラーが発生する場合には、下記の 3点の対処方法があります。
いずれの方法を採用するかは、のアプリケーションの開発方針などに合わせて、ご検討いただくことができます。
1) データ型を一致させる
データ型を一致させることで、暗黙的な変換が発生しなくなるため、データの切り捨ても発生しなくなります。
また、参考とはなりますがデータ型を一致させることは、参照系の処理においても、パフォーマンスの維持の観点など、有効なポイントとなりますので下記のブログも併せて、ご確認ください。
DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる
2) 明示的にデータ型を変換する
データ型、データ長が異なるデータを INSERT、UPDATE する場合には、暗黙的な変換に頼らず、INSERT/UPDATE 対象のテーブルのデータ型、データ長に明示的に変換することが対処となります。
例2では、次のようにCONVERTによって明示的にデータ長を変換します。
| INSERT INTO table01(C1) SELECT CONVERT(varchar(10), table02.C1) FROM table02 INNER JOIN table03 ON (table02.C1 = table03.C1) WHERE table02.C2 = 1 |
3) ANSI_WARNINGS を OFF にする
ANSI_WARNINGS では、INSERT 又は UPDATE にて暗黙的な切り捨てが発生した場合、エラーとして中断する（ON）か、クエリを継続する（OFF）かを設定します。そのため、SET ANSI_WARNINGS OFF と指定することで、対処となります。
注意点としては、既定の設定は使用しているデータアクセス プロバイダーにより異なり、多くのプロバイダーでは ISO 基準に準拠するため、接続時に自動的に ANSI_WARNINGS が ON に設定されます。
下記公開情報に記載されているドライバ以外では、.NET Framework データ プロバイダーを使用した場合も接続時に自動的に ANSI_WARNINGS が ON に設定されます。
そのため、SQL Server 側では対応ができず、接続を確立後に明示的に、ANSI_WARNINGS を OFF にする必要がございます。
SET ANSI_WARNINGS (Transact-SQL)
---＜抜粋＞---
SQL Server Native Client ODBC ドライバーおよび SQL Server Native Client OLE DB Provider for SQL Server では、接続時に自動的に ANSI_WARNINGS が ON に設定されます。
この構成は、ODBC データ ソースまたは ODBC 接続属性で定義され、接続前にアプリケーションで設定できます。 DB-Library アプリケーションからの接続に対しては、既定では SET ANSI_WARNINGS は OFF に設定されています。
---＜抜粋＞---
※2) と 3) の対処を行った場合、データによっては切り捨てが行われた状態で、条件比較が実施される可能性がございますので、十分に検証いただくようにお願いいたします。
対象
SQL Server 2008 R2 までは下記の通り、公開情報での記載がありましたが、SQL Server 2012 や SQL Server 2014 においても発生しうる事象となります。
データ型の変換 (データベース エンジン)
*****
SQL Server がデータのデータ型を自動的に変換します。たとえば、smallint 型を int 型と比較する場合、比較を実行する前に、smallint 型から int 型に暗黙的に変換されます。クエリ オプティマイザーでは、クエリ プランを生成してこの変換をいつでも実行できます。その結果、精度の低下、数値以外の文字列の数への変換などの変換エラーが実行時に発生する場合があります。
*****