On a SQL Server 2008 R2 DB, I'm running an aggregation query every hour or so, on data that's being constantly inserted into a table, and what I'd like to do is pull all the "new" records that were inserted, since the last time that query ran. An identity column can generally do this, since you can assume it's monotonically increasing, but according to this post (https://stackoverflow.com/questions/2828936/does-sql-server-guarantee-sequential-inserting-of-an-identity-column), you're not necessarily guaranteed that when you do the pull, that you've pulled every record before the record with the greatest id.
Question is, how can I do this? A timestamp with default value seems reasonable, but also seems to fail in my mind for similar reasons. Why? Well if the default value of a time column is [CurrentTime], then could it not happen that with parallel inserts, insert #1 has a greater timestamp than insert #2, if insert #1 is slightly delayed? I guess it really depends on when the time is pulled vs when the row is inserted: something which I have no insight into.
The only solution I can fathom that will avoid this issue is if I pull all records from [LastTimeJobRan] - [SomeConstantOffset] up to [CurrentTime] - [SomeConstantOffset]. In other words, assume I run the job at 1:05, I would then pull all records from 12:00 - 1:00, hoping that 5 minutes would be enough of a window to avoid any "stalls" from inserting (obviously a few seconds would suffice here methinks). From a high level, I'm basically staggering/delaying processing of the latest records that are currently being inserted.
The other solution I'm thinking of involves a combination of storing the time of the last run, and marking records as being "processed". The last time run (offset by a bit as well perhaps) is just an optimization to avoid pulling a lot of old rows that have already been marked.
Am I overthinking this problem? Are there guarantees that the DB makes that obviate this entire line of thought?
I'm sure this is a common enough problem, but I couldn't find much relating to this besides the above SO post. Perhaps I'm using the wrong keywords.