I have a very large table and I must delete big amount of data from it until some point.

The table has only one non-clustered index on a column which is not relevant to use in my delete query. The first idea that comes to me is to include to the index a unique column which I could use in my delete query.

My question is, do you think it will be a good option or something better can be done?

  • 2
    very large table and delete big amount: drop the non-clustered index, run the delete and recreate the index. – dnoeth Oct 20 '16 at 8:03
  • Will my query run faster after dropping the index than it could ? – Nizami Khalilbayli Oct 20 '16 at 8:10
  • 1
    Probably, this is a common technique when deleting a large percentage. Instead of maintaining the index during the delete you simply recreate it when it's done. – dnoeth Oct 20 '16 at 8:30
  • how long can it take to recreate the index in a table with data space for more than 100 GB ? – Nizami Khalilbayli Oct 20 '16 at 9:11
  • How long can it take to maintain that index during a delete? You can run some tests on a smaller subset of the data. – dnoeth Oct 20 '16 at 9:24

You should do 2 things for efficiently deleting the data from your big table :

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.