I have a production system that has a large dataset of about 2 million rows. I need to create an index on a property but don't want it available to queries until the index is fully populated. Is there a way I can create the indexed, fire off the build, then "activate" the index so queries can use it.
The problem I face is the minute I create the index queries start returning zero rows because it starts using the index. As I start building it starts returning qualifying rows for those it has indexed but misses those not yet processed. All of this creates incorrect results while the build is running. I don’t have the luxury of being able to disable access until the index is built.
I don’t want to have to modify the existing queries to tell them to exclude using this index as there are too many to change, then change back.