I'm trying to imagine what it would take to craft a delayed indexing mechanism for MS SQL Server.
The basic functionality I'm looking for:
- happens outside of the original transaction that inserts or updates the data and therefore does not slow it down
- has very low priority and therefore only works at idle time
- does not have to support transactions
Quick googling around shows that for some reason such a seemingly trivial feature has never been implemented, not natively anyway.
The easiest way to craft it of course is to use a trigger to capture the data updates and enqueue them into a DataBroker queue (or even to another table) which would then be looked at by a JobScheduler job that would insert/update data to an indexed table. Not sure if such a 2-table scheme allows for transparent selects though (you would have to first look in the indexed table and if not found fallback to the unindexed table), but maybe we can employ some View and/or User Defined Function magic, which may work for certain access patterns, like if we only ever expect maximum one result row returned...
Isn't there a NORMAL solution though?
Tags: