0

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?

Improve this question
7
  • 2
    very large table and delete big amount: drop the non-clustered index, run the delete and recreate the index.
    – dnoeth
    Oct 20, 2016 at 8:03
  • Will my query run faster after dropping the index than it could ?
    – Nizami Khalilbayli
    Oct 20, 2016 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, 2016 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, 2016 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, 2016 at 9:24

1 Answer 1

Reset to default
3

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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