Transaction isolation level - Read committed and select on large tables
In general, the storage engine has two options to perform a clustered index scan: an allocation order scan or an index order scan. For an allocation order scan, the storage engine reads the data in file order. In your scenario, this could result in the data from the insert showing up in the first query. For an index order scan, the storage engine reads data in linked list order. In your scenario, the new data from the insert will not show up in the first query. Even if the page that contains the new row splits the new page will not be read.

The storage engine decides between an allocation order scan or an index order scan depending on the requirements of the query and the isolation level. For example, for a query that requires data to be in order an index order scan is the right choice. For a query with a table lock that does not require the data to be in order, an allocation order scan is the right choice. Specifically, you will end up with an allocation order of the unsafe category (a scan can return multiple occurrences of rows or skip rows when page splits occur) when all of the following are true:


The query is running under the read uncommitted isolation level
Changes are allowed to the data (a table lock was not taken by the query)
The plan shows Index Scan, Ordered: false (the query does not require the data to be sorted)
The index size is greater than 64 pages.


In your example you have an isolation level of read committed. You also did not take a table lock with your clustered index scan query because the second session was able to insert a row. Therefore, the storage engine will perform a scan of the index order scan variety. The new data from the insert will not show up in the first query that does the scan.

My source for this is pages 65-69 from T-SQL Querying by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee.