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:
... 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
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
No comments:
Post a Comment