The crux of the problem: 1) you batch insert into a table using a transaction (say 1000 rows) 2) another query simultaneously reads from same table 3) sometimes the query returns partial results - e.g. instead of 1000 rows being returned you get 600
My understanding was a query should return either all 1000 rows or none. I am not using anything other than the default settings (no read uncommitted) This question has already been asked: https://dba.stackexchange.com/questions/75848/sql-server-select-while-insert-on-another-transaction-gives-unexpected-results However I'm asking here to get some more feedback <looks longingly in Dr Low's direction> This kind of behaviour strikes at the core of my (apparent) understanding! I've been able to recreate this easily on sql 2012 SP3 but not yet on 2014 or 2016.
