How to improve the efficiency of SQLServer Delete data
Scene:
Delete a large table of 300 million.
It is realized through the intermediate table.
First write the data that needs to be archived into the history table Cou_History..Cou_History.
Write the field conditions that need to delete the table to the intermediate table.
Select SysNo into COU_temp01
FROM COU
WHERE EXISTS (SELECT 1 FROM Cou_History..Cou_History a WHERE a.SysNo=COU.SysNo)
Then use the intermediate table as the intermediate condition to delete the relevant data of the main table. Then delete the intermediate table field data that has been deleted in the main table.
The following SQL loop is executed until all have been deleted.
DELETE TOP (1000)
From COU
Where SysNo in
(select top 1000 * from COU_temp01)
Delete top (1000) from COU_temp01
