Andy Richards · 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.



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