A bizarre situation - SQL query finds no records when record is present.

First thing to look would be "Dynamic cursor".

What this looks like from a DBA point of view is not really a statistics or optimizer issue. It’s more likely tied to how the dynamic cursor is maintaining its internal state under read committed snapshot isolation, especially while the table is being torn down and rebuilt row by row.

During those delete and reinsert cycles, you effectively end up with a moving target from a versioning and visibility perspective. SQL Server is constantly switching between row versions and a dynamic cursor is trying to maintain a live membership set over that changing data. That combination can occasionally leave you with a cursor state that doesn't fully reflect what’s already committed in the base table at that exact moment.

What makes it more confusing is that when you hit the base table directly, you’re essentially forcing a fresh access path and a new execution context. That tends to flush out any cached cursor state or forces SQL Server to re-evaluate the access path cleanly. After that happens, the view query starts behaving normally again because everything is effectively resynchronized.

So what you end up seeing is not really "missing data" but a temporary mismatch between the cursor's internal working set and the actual current state of the table while row versioning and continuous deletes/reinserts are happening in the background.