Post reply

Delete records faster

  • March 7, 2014 at 4:53 am

    #150078

    What is the best of removing the data from the table.

    Dont want any row from the table.(this is runtime usecase,so instead of delete, going for Truncate option).

    Table is having some 5 million records.

    Master table and child table are inter related with forigen keys.

    So remove fk and have truncate statement and again enable fk.

    i have some idea of removing the data

    Step1: EXEC ('ALTER TABLE ' @Child_Tbl + 'DROP CONSTRAINT ' + @FK_Name + '');

    Step2: TRUINCATE TABLE @Child_Tbl;

    Step3: EXEC ('ALTER TABLE ' @Child_Tb + ' ADD CONSTRAINT ' + @FK_Name + ' FOREIGN KEY (' + @Child_Col + ') REFERENCES ' @Parent_Tbl + ' (' + @Parent_Col + '));

    Step1 is running and it successed to Step2 if someone kills the current session then when step2 is running

    what will happen?

    How to reenable the fk to table again in this case.

    Is this a best option or we can go with delete operation so that even any break happen there wont be much problem(but log size will be big).

    Thanks!

  • March 7, 2014 at 7:28 am

    #1695115

    In simple terms, wrap the three steps in an explicit transaction.

    BEGIN TRANSACTION

    step 1;

    step 2;

    step 3;

    COMMIT;

    There may be more to this if you are looking at adding any kind of error handling to the process. Since you appear to be using SQL Server 2000 at this point, I will leave addressing that to others as it has been years since I have worked with that version of SQL Server.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply