最初は、20億件のレコードが大量にあるようです。 しかし、毎日1,000万の新しいエントリが追加された場合はどうでしょうか？ そして、10億個以上の値がすでに使い果たされていますか？ 年中無休のアプリケーションはありますか？ その後、主キーのタイプを修正するために残っているのはわずか114日です。 Webアプリケーションとクライアントの両方でキー値を使用する場合、これはそれほど重要ではありません。
記載されている状況が馴染みがあり、この不幸な詳細に気づいた場合-主キー値が不足している-遅すぎる場合、この記事はあなたに適しています。 この記事には、TableWithPKViolationテーブル用に提供されているスクリプトがあり、TableWithPKViolationIdフィールドが問題の原因となっています。
最悪の場合、「IDENTITYをデータ型intに変換する算術オーバーフローエラー」というエラーが発生しました。 これは、主キー値の有効期限がすでに切れており、アプリケーションの動作が停止したことを意味します。 この場合、次のソリューションを使用できます。
- 主キーのタイプをBIGINTに変更します。 誰もが、最良の選択肢は過去にタイムマシンに入り、INTをBIGINTに変更することであることを理解しています。 しかし、サーバーおよびクライアントアプリケーションでTableWithPKViolationIdフィールドが使用されていない場合は、すぐにそれを行うことができます。そうすれば、すばやく簡単にタイプを変更できます。 これを行い、記事の残りの時間を無駄にしないでください。 テーブルに10億を超えるレコードがある場合、変更が適用されることに注意してください。 サーバーの容量によっては3時間以上かかる場合があり、トランザクションログに追加のスペースが必要になります（可能な場合は、シンプルで復旧モードモデルに切り替えます）。 変更のスクリプトは次のとおりです。
ALTER TABLE TableWithPKViolation ALTER COLUMN TableWithPKViolationId BIGINT;
この方法を使用できない場合は、できるだけ早くBIGINTへの切り替えを計画する必要があります。
- 負の値を使用してください。 通常、IDを使用すると、デフォルトでIDENTITY（1,1）が使用されます。 値が20億レコードに近づくと、次のコマンドを使用して初期値をリセットできます。
DBCC CHECKIDENT (TableWithPKViolation, - 2147483647, reseed)
そのため、BIGINTに切り替えるための時間が長くなります。 このソリューションの唯一の不便は、主キーの負の値です。 ビジネスロジックが否定的であることを確認します。 おそらくこれが最も簡単なソリューションです。
- III。 未使用の値でテーブルを作成します。 欠落している値をカウントし、未使用の値のリストを持つテーブルを形成します。 これにより、BIGINTに切り替える時間が追加されます。
この方法は、テーブル内のエントリの順序に依存しない場合、つまりORDERY BY Idを使用しない場合に適しています。 または、そのような並べ替えが行われる場所は多くありません。たとえば、レコードが追加された日付に、並べ替えを別のフィールドに変更できます。
未使用の値を持つテーブルを作成するには、2つの方法があります。
方法A 欠損値。
Identityを使用する場合、値はトランザクションの開始時に予約されるため、常に欠損値があります。また、ロールバックの場合、次のトランザクションには予約されたものに続く新しい主キー値が割り当てられます。 キャンセルされたトランザクションに対して生成された予約値は未使用のままになります。 これらの未使用の値は、別のテーブルに形成し、以下に示すコードを使用して適用できます。
方法B 削除された値。
通常、主キー値が終了するテーブルからレコードを削除する場合、削除されたすべての値は無料の値として再利用できます。 このオプションのサンプルコードを以下に示します。
ソーステーブルTableWithPKViolation。
CREATE TABLE [dbo].[TableWithPKViolation]( [TableWithPKViolationId] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY]
1.無料IDを保存するためのテーブルを作成します
10-CreateNewId.sql
CREATE TABLE [dbo].[NewIds]( [NewId] [int] NOT NULL, [DateUsedUtc] [datetime] NULL ) ON [PRIMARY]
さらに、方法に応じて：
メソッドAでシーケンスを生成するには：欠損値：
2.欠落している識別子のシーケンスを生成します
20-GenerateGaps.sql
「Option1 FindGaps \ 20-GenerateGaps.sql」
CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFromGaps] @batchsize INT = 10000, @startFrom INT = NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF @startFrom IS NULL BEGIN SELECT @startFrom = MAX([NewId]) FROM dbo.NewIds; END; DECLARE @startId INT = ISNULL(@startFrom,0); DECLARE @rowscount INT = @batchsize; DECLARE @maxId INT; SELECT @maxId = MAX(TableWithPKViolationId) FROM dbo.TableWithPKViolation; WHILE @startId < @maxId BEGIN INSERT INTO dbo.NewIds ([NewId]) SELECT id FROM ( SELECT TOP (@batchsize) @startId + ROW_NUMBER() OVER(ORDER BY TableWithPKViolationId) AS id FROM dbo.TableWithPKViolation --any table where you have @batchsize rows ) AS genids WHERE id < @maxId AND NOT EXISTS ( SELECT 1 FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK) WHERE Tb.TableWithPKViolationId = genids.id ); SET @rowscount = @@ROWCOUNT; SET @startId = @startId + @batchsize; PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50)); END END
メソッドBリモート値のシーケンスを生成するには：
2.テーブルを作成してシーケンスを生成し、1〜2,147,483,647のデータを入力します
15-CreateInt.sql
CREATE TABLE [dbo].[IntRange]( [Id] [int] NOT NULL ) ON [PRIMARY]
20-GenerateInt.sql
CREATE PROCEDURE [dbo].[spNewIDPopulateInsert] @batchsize INT = 10000, @startFrom INT = NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF @startFrom IS NULL BEGIN SELECT @startFrom = MAX(id) FROM dbo.IntRange; END; DECLARE @startId INT = ISNULL(@startFrom,0); DECLARE @rowscount INT = @batchsize; DECLARE @maxId INT = 2147483647; WHILE @rowscount = @batchsize BEGIN INSERT INTO dbo.IntRange (id) SELECT id FROM ( SELECT TOP (@batchsize) @startId + ROW_NUMBER() OVER(ORDER BY TableWithPKViolationId) AS id FROM dbo.TableWithPKViolation --any table where you have @batchsize rows ) AS genids WHERE id < @maxId; SET @rowscount = @@ROWCOUNT; SET @startId = @startId + @rowscount; PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50)); END END
25-PopulateRange.sql
exec dbo.spNewIDPopulateInsert @batchsize = 10000000
このスクリプトは、TableWithPKViolationテーブルを使用してシーケンスを生成しますが、MS SQL（シーケンス）に組み込まれたシーケンスを含む、このための任意のメソッドを使用できます。 この方法が選択されたのは、他の方法に比べて高速に機能するためです。
30-CreateIndexOnInt.sql
ALTER TABLE [dbo].[IntRange] ADD PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
そしてそれを埋める
50-GenerateNewId.sql
CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFiltered] @batchsize INT = 10000, @startFrom INT = NULL, @endTill INT = NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF @startFrom IS NULL BEGIN SELECT @startFrom = MAX([NewId]) FROM dbo.NewIds; END; DECLARE @startId INT = ISNULL(@startFrom,0); DECLARE @rowscount INT = @batchsize; DECLARE @maxId INT = ISNULL(@endTill,2147483647); DECLARE @endId INT = @startId + @batchsize; WHILE @startId < @maxId BEGIN INSERT INTO [NewIds] ([NewId]) SELECT IR.id FROM [dbo].[IntRange] AS IR WHERE IR.id >= @startId AND IR.id < @endId AND NOT EXISTS ( SELECT 1 FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK) WHERE Tb.TableWithPKViolationId = IR.id ); SET @rowscount = @@ROWCOUNT; SET @startId = @endId; SET @endId = @endId + @batchsize; IF @endId > @maxId SET @endId = @maxId; PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50)); END END
55-ExecGeneration.sql
-----Run each part in separate window in parallel ----- --part 1 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1, @endTill= 500000000 --end of part 1 --part 2 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 500000000, @endTill= 1000000000 --end of part 2 --part 3 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1000000000, @endTill= 1500000000 --end of part 3 --part 4 DECLARE @maxId INT SELECT @maxId = MAX(TableWithPKViolationId) FROM dbo.TableWithPKViolation exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1500000000, @endTill= @maxId --end of part 4
3.メソッドAまたはBによって生成された無料の識別子のテーブルは準備ができています。 空きキーを持つテーブルにインデックスを作成します
60-CreateIndex.sql
ALTER TABLE [dbo].[NewIds] ADD CONSTRAINT [PK_NewIds] PRIMARY KEY CLUSTERED ( [NewId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_NewIds_DateUsedUtc] ON [dbo].[NewIds] ( [DateUsedUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) GO ALTER TABLE [dbo].[NewIds] SET ( LOCK_ESCALATION = DISABLE ) GO
すべてが正しく生成されたことを確認します。 メインテーブルTableWithPKViolationにあるNewIdテーブルにはIDがありません。
70-CheckData.sql
declare @maxId INT select @maxId = max(TableWithPKViolationId) from [dbo].[TableWithPKViolation] IF EXISTS (select 1 from [dbo].[NewIds] WHERE [NewId] > @maxId) BEGIN PRINT 'PROBLEM. Wait for cleanup'; declare @batchsize INT = 10000 DECLARE @rowcount int = @batchsize; while @rowcount = @batchsize begin delete top (@batchsize) from [dbo].[NewIds] where DFVId > @maxId; SET @rowcount = @@rowcount; end; END ELSE PRINT 'OK';
別のサーバー（データベースのバックアップが復元されたサーバーなど）で順次生成する場合、スクリプトを使用してデータをファイルにアップロードできます。
80-BulkOut.sql
declare @command VARCHAR(4096), @dbname VARCHAR(255), @path VARCHAR(1024), @filename VARCHAR(255), @batchsize INT SELECT @dbname = DB_NAME(); SET @path = 'D:\NewIds\'; SET @filename = 'NewIds-'+@dbname+'.txt'; SET @batchsize = 10000000; SET @command = 'bcp "['+@dbname+'].dbo.NewIds" out "'+@path+@filename+'" -c -t, -S localhost -T -b '+CAST(@batchsize AS VARCHAR(255)); PRINT @command exec master..xp_cmdshell @command
4.適切な量の利用可能なIDをマークし、結果として返すプロシージャを作成します
90-GetNewId.sql
create PROCEDURE [dbo].[spGetTableWithPKViolationIds] @batchsize INT = 1 AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @rowcount INT, @now DATETIME = GETUTCDATE(); BEGIN TRAN UPDATE TOP (@batchsize) dbo.NewIds SET DateUsedUtc = @now OUTPUT inserted.[NewId] WHERE DateUsedUtc IS NULL; SET @rowcount = @@ROWCOUNT; IF @rowcount != @batchsize BEGIN DECLARE @msg NVARCHAR(2048); SET @msg = 'TableWithPKViolationId out of ids. sp spGetTableWithPKViolationIds, table NewIds. ' +'Ids requested ' + CAST(@batchsize AS NVARCHAR(255)) + ', IDs available ' + CAST(@rowcount AS NVARCHAR(255)); RAISERROR(@msg, 16,1); ROLLBACK; END ELSE BEGIN COMMIT TRAN END; END
5.データがテーブルに挿入されたすべてのプロシージャに追加し、SCOPE_IDENTITY（）、新しいプロシージャコールを返します。
生産性が許すか、時間が非常に高価で、多くの手順を変更する必要がある場合は、挿入の代わりにトリガーを作成できます。
手順を使用して新しい主キー値を発行する方法の例を次に示します。
CREATE TABLE #tmp_Id (Id INT); INSERT INTO #tmp_Id EXEC spGetTableWithPKViolationIds @batchsize=@IDNumber; SELECT @newVersionId = Id FROM #tmp_Id; SET IDENTITY_INSERT [dbo].[TableWithPKViolation] ON;
SET IDENTITY_INSERT ONオプションでは、呼び出し元がTableWithPKViolationテーブルに対するALTERパーミッションを持っている必要があることに注意してください。
6.次に、JOBを設定できます。これにより、使用されている識別子でテーブルがクリアされます
95-SPsCleanup.sql
create PROCEDURE dbo.spCleanupNewIds @batchSize INT = 4999 AS BEGIN SET NOCOUNT ON DECLARE @minId INT DECLARE @maxId INT SELECT @minId = Min([NewId]), @maxId = MAX([NewId]) FROM dbo.NewIds WITH (NOLOCK) WHERE DateUsedUtc IS NOT NULL; DECLARE @totRowCount INT = 0 DECLARE @rowCount INT = @batchSize WHILE @rowcount = @batchsize BEGIN DELETE TOP (@batchsize) FROM dbo.NewIds WHERE DateUsedUtc IS NOT NULL AND [NewId] >= @minId AND [NewId] <= @maxId SET @rowcount = @@ROWCOUNT SET @totRowCount = @totRowCount + @rowcount END PRINT 'Total records cleaned up - ' + CAST(@totRowCount AS VARCHAR(100)) END
1日1回使用済みレコードを削除するJOBはオプションです。 メインテーブルからレコードを定期的に削除する場合は、削除された値でこのテーブルを補完できます。
私はまだBIGINTへの切り替えを計画することをお勧めします。
もちろん、新しい識別子は昇順で発行されますが、新しい識別子の算術値が小さくても、以前に発行された古い識別子に従うように、新しい識別子を並べ替えるロジックを検討する必要があります。
主キーの値が突然なくなるという問題に対するこの記事で説明する一時的な解決策は、システムとプログラムを新しいデータ型に変更している間、時間を稼ぎ、システムを動作可能な状態に保つのに役立ちます。
最善の解決策は、境界値を監視し、適切なデータ型に事前に切り替えることです。
コードでアーカイブする