· Jun 1, 2016

Activating an index

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.



Discussion (3)0
Log in or sign up to continue