Friday, September 16, 2016

SQL Server: GO with count | delete table in chunks

Hi,

I was asked to delete parts of a table. Unfortunately the amount of rows was to big to delete them all in a single shot (TLog would exceed the file limit). I tried a different approach deleting the table in chunks using a loop, but this still executes as a single transaction.

Then I thought I could solve the problem by using a top statement and using go... afterwards I would copy these lines some thousand times and go for a coffee... but than the msdn article about go ( https://msdn.microsoft.com/en-us/library/ms188037.aspx ) opened up a very nice alternative.

Go can have an argument "count" with the following description:

count
Is a positive integer. The batch preceding GO will execute the specified number of times.

... nice, but how will it be executed? Statement-Block COUNT times and then one go (same problem like in the loop scenario) or is it a COUNT times series of statement, go, statement, go,...

I was not able to find the answer so I checked it myself with the following code:

waitfor delay '00:01:00'
go 3

the session-details of the activity monitor showed the single waitfor - statement. Proof enough that go with count creates a statement-go-statement-go series which perfectly solves my problem of the first section (see https://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx ).

delete top (5000) from table where x = 123;
go 10000

kind regards,
Daniel