All we need is an easy explanation of the problem, so here it is.
Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to tell sql to keep index data(+ handle updates\inserts) without using it?
So if needed it can be "enabled" immediately?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Other than using query hints to force different indexes, or using Query Store to force plans that don’t use the index, no. There’s no way to "hide" an enabled index from the query optimizer.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂