III. . , , . BIGINT.
, , ORDERY BY Id. , , , , , .
:
. .
Identity, , , , , , , , . , , . , , .
. .
, , . .
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]
, :
. :
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 (Sequence), , .
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
, , ID NewId, TableWithPKViolation.
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(), .
, , instead of insert.
, , :
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 , ALTER TableWithPKViolation.
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
JOB, , . , , .
BIGINT.
, , , , , , .
, , , , , .
