Select statement on a table with constant inserts This is a dumb question. I was wondering how sql server works in this situation. Lets say I am selecting from a table ( select * from tab) and the table is having hundreds of rows getting inserted per second, how does sql server know when to stop reading the rows?

Edit: Just to add, I did a select on a 200Gb table and monitored the locks on another session with sp_lock and saw that sql server was grabbing page shared lock on one page at a time and reading data. So wondering how does it know when to stop? Does sql server take into consideration when the select started and select only the pages that are committed when the transaction starts?
It depends on transaction isolation level.

Your class of problem is called "phantom read", and will only be circumvented when your transaction isolation level is "serializable" or above. SQL Server has a default level of "read comitted", so you can read "too few" rows.

See https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads .