Sample Header Ad - 728x90

Deleting large amounts of rows in SQL server 15

0 votes
0 answers
59 views
I'm trying to execute a batch delete, but I'm having trouble executing it due to not properly using clustered index. Clustered index consists of three fields(int, int, datetime2). The table consists of four columns(PK[integer, integer, datetime2], decimal) What I have tried so far is doing TOP(X) delete which in the end slows down alot and deleting by days (one batch is one day) which is also not a good solution. Below I have attached the "by day" deletion script.
DECLARE
    @ITERATION INT = 0
    , @TOTALROWS INT = 0
    , @MSG VARCHAR(500)
    , @STARTTIME DATETIME
    , @ENDTIME DATETIME
    , @StartValue DATETIME = (SELECT MIN(DateTimeValue) FROM TableX)
    , @EndValue date
    , @MaxValue date = '2022-04-12';

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

WHILE @StartValue = @StartValue AND DateTimeValue > ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR);

    RAISERROR (@MSG, 0, 1) WITH NOWAIT;

    SET @StartValue = @EndValue;

END;
Is there any way to use clustered index to my advantage here to speed up deletion?
Asked by elitra (1 rep)
Jul 12, 2022, 03:13 PM
Last activity: Jul 12, 2022, 03:35 PM