Aggregating data - Getting data inserted after last time query was run 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.
This is a classic senario that we experienced with out datawarehouse. We already had timestamp column, but still to process new records from the last time the query ran was difficult just like you are currently facing.

What we did was - we added 3 columns - LATEST, END_OF_DAY and PROCESSED columns to our main table where the data was loaded for further processing.

LATEST = All the data that gets loaded is defaulted to 1. Meaning that the data is latest.

PROCESSED = Will be set to 1 for the data that is already read and processed by our ETL job. The new data will be having a default of 0.

END_OF_DAY = This is another flag that is useful just in-case if you want to process all the records for the entire day. Just our requirement. Your case might be different.

Note: Above is just a simple example of how a sort of similar issue like you mentioned was addresses. Obviously there were many factors and business requirements that lead us to introduce such extra fields.

Also, you can use Calendar Table and Date/Time Functions to supplement what I mentioned above.

On the top of above, I would suggest to look into Data Loading Performance Guide to get benefit when the data is loaded and the Isolation level (especially RCSI) of the database as if the data is being continuously loaded then there would be blocking issues. Test it, balance it and then implement it !!