clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591556 fiddles created (45730 in the last week).

CREATE TABLE dbo.MyBigTable ( MyKey bigint NOT NULL, x char(1), -- OtherColumns here, CONSTRAINT PK_MyBigTable PRIMARY KEY (MyKey) ); -- Partition stuff here INSERT dbo.MyBigTable(MyKey) SELECT TOP (95008) ROW_NUMBER() OVER (ORDER BY s1.object_id) FROM sys.all_objects AS s1 CROSS JOIN (SELECT TOP (1000) * FROM sys.all_objects) AS s2; DELETE dbo.MyBigTable WHERE MyKey IN (10, 50005)
95010 rows affected
 hidden batch(es)


DECLARE @BatchSize int = 10000; ;WITH x AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY MyKey), s = FIRST_VALUE(MyKey) OVER (ORDER BY MyKey), ln = ROW_NUMBER() OVER (ORDER BY MyKey DESC), MyKey FROM dbo.MyBigTable ) SELECT BatchNumber = ROW_NUMBER() OVER (ORDER BY rn), RangeStart = COALESCE(LAG(MyKey,1) OVER (ORDER BY MyKey)+1,s), RangeEnd = MyKey, Processed = 0 INTO dbo.UpdatingQueue FROM x WHERE rn % @BatchSize = 0 OR ln = 1 ORDER BY BatchNumber; CREATE UNIQUE CLUSTERED INDEX CIX_UpdatingQueue ON dbo.UpdatingQueue(RangeStart, RangeEnd);
10 rows affected
 hidden batch(es)


-- now your batch can say: DECLARE @BatchNumber int; SELECT @BatchNumber = MIN(BatchNumber) FROM dbo.UpdatingQueue WHERE Processed = 0; WHILE @BatchNumber IS NOT NULL BEGIN -- need try/catch here obviously UPDATE mbt SET x = 'x' FROM dbo.MyBigTable AS mbt INNER JOIN dbo.UpdatingQueue AS q ON mbt.MyKey >= q.RangeStart AND mbt.MyKey <= q.RangeEnd WHERE q.BatchNumber = @BatchNumber AND q.Processed = 0; UPDATE dbo.UpdatingQueue SET Processed = 1 WHERE BatchNumber = @BatchNumber; SET @BatchNumber += 1; SELECT @BatchNumber = MIN(BatchNumber) FROM dbo.UpdatingQueue WHERE Processed = 0; -- add a delay, explicit checkpoint / backup log here, what have you END -- and the batch can be interrupted, because if -- you stop it and start it again tomorrow or -- next week, it will pick up where it left off.
95016 rows affected
 hidden batch(es)


-- spot check SELECT * FROM dbo.MyBigTable WHERE MyKey % 1000 = 0;
MyKey x
1000 x
2000 x
3000 x
4000 x
5000 x
6000 x
7000 x
8000 x
9000 x
10000 x
11000 x
12000 x
13000 x
14000 x
15000 x
16000 x
17000 x
18000 x
19000 x
20000 x
21000 x
22000 x
23000 x
24000 x
25000 x
26000 x
27000 x
28000 x
29000 x
30000 x
31000 x
32000 x
33000 x
34000 x
35000 x
36000 x
37000 x
38000 x
39000 x
40000 x
41000 x
42000 x
43000 x
44000 x
45000 x
46000 x
47000 x
48000 x
49000 x
50000 x
51000 x
52000 x
53000 x
54000 x
55000 x
56000 x
57000 x
58000 x
59000 x
60000 x
61000 x
62000 x
63000 x
64000 x
65000 x
66000 x
67000 x
68000 x
69000 x
70000 x
71000 x
72000 x
73000 x
74000 x
75000 x
76000 x
77000 x
78000 x
79000 x
80000 x
81000 x
82000 x
83000 x
84000 x
85000 x
86000 x
87000 x
88000 x
89000 x
90000 x
91000 x
92000 x
93000 x
94000 x
95000 x
 hidden batch(es)