I am trying to troubleshoot an issue in a report and wondering if the below scenario is possible. I have a tables which is 548GB and there are reports which sometimes select 5 years of data which could be alomost the entire table. The table has a non unique clustered index.
I read about read committed isolation levels and was testing it to see how it was locking the table during selects and from my understanding it looks like it puts a series of shared page lock. Acquire shared page lock, read page, release shared page lock, move to next page and so on.
If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past. What I mean by beginning of table is that since the table is having a clustered index and since it has to be ordered if an lower value index id record gets inserted then it needs to be inserted into the starting half of the leaf level of the clustered index.