Best approach to delete first 5 million rows from a heap in batches
The subject is pretty self explanatory. But what we want is to keep the size of the log file minimal with optimum performance. I am not sure whether it is possible as there is no clustered index and hence no specific order of the data?
I have just got the good news that source text files for the heap are available at remote site. Those would be approximately 100K size each. So here it is what we are planning to do A.Create a clustered index on heap for all the eight columns as they make a primary key B.Import the data from one file in a Staging table C.Create a Unique Clustered Index on all the eight columns of the Staging table
D.Do the batch delete from the heap against the staging table The steps from B to D will repeat for every file. We are also planning to re-import all the data, if all the files are found. But this time with an identity column and a column which would store the file name. I will appreciate any valuable suggestions to improve the process.