[解決済み] SQL Server データベースで ID のインクリメントが飛ぶ

質問

私のテーブルの1つで Fee のカラム "ReceiptNo"で、SQL Server 2012 データベースの ID 増分が、次の 2 つの事柄によって、突然 1 ではなく 100 にジャンプするようになりました。

1. が 1205446 であれば 1206306 に、1206321 であれば 1207306 に、1207314 であれば 1208306 にジャンプします。ここで注意していただきたいのは、次の図に示すように、ジャンプが発生しても最後の3桁は一定、すなわち306のままであることです。

2. この問題は、コンピュータを再起動したときに発生します。

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

SQL Server 2012 以降のパフォーマンスの向上により、この動作が発生します。

を割り当てる際に、デフォルトで 1,000 のキャッシュ・サイズを使用するようになりました。 IDENTITY の値を int カラムの値を削除してサービスを再起動すると、未使用の値が失われることがあります (キャッシュサイズは bigint / numeric ).

に記載されています。 のドキュメントに記載されています。

SQL Server はパフォーマンス上の理由から ID 値をキャッシュすることがあります。 割り当てられた値の一部は、データベース障害またはサーバーの再起動時に失われる可能性があります。 サーバーの再起動時に失われることがあります。このため、挿入時に ID 値にギャップが生じることがあります。 挿入時に ID 値にギャップが生じることがあります。ギャップが許容できない場合、アプリケーションは独自のメカニズムでキー値を生成する必要があります。 を使用してキー値を生成する必要がある。シーケンスジェネレータを使用し を使用する。 NOCACHE オプションでシーケンスジェネレータを使用すると、決してコミットされないトランザクションにギャップを制限することができます。 を制限することができます。

表示されたデータから、12 月 22 日のデータ入力後に発生し、SQL Server が再起動したときに値が予約されたように見えます。 1206306 - 1207305 . 12 月 24 日と 25 日のデータ入力が行われた後、再度再起動し、SQL Server は次の範囲を予約しました。 1207306 - 1208305 28 日のエントリで表示されます。

異常な頻度でサービスを再起動しない限り、quot;lost" 値がデータ型によって許容される値の範囲に大きな影響を与えることはほとんどないため、最善の策はそれを気にしないことです。

これが何らかの理由で本当に問題である場合、いくつかの可能な回避策があります。

1. を使用することができます。 SEQUENCE を使用し、より小さなキャッシュサイズを定義することができます。 NEXT VALUE FOR をカラムのデフォルトで使用します。
2. またはトレースフラグ272を適用して IDENTITY アロケーションがログに記録されるようにします。これは、すべてのデータベースに対してグローバルに適用されます。
3. または、最近のバージョンでは、以下を実行します。 ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF を実行して、特定のデータベースに対する ID キャッシュを無効にすることもできます。

これらの回避策はどれもギャップがないことを保証するものではないことに注意する必要があります。これは IDENTITY テーブルへの挿入をシリアライズすることによってのみ可能であるためです。もし隙間のないカラムが必要な場合は、次のいずれでもなく別の解決策を使用する必要があります。 IDENTITY または SEQUENCE