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).