My application creates cyclically query, which always consists of 100 lines of UPDATE. Each UPDATE also modify column time_of_write using SQL function GETDATE (). I suppose that SQL Server performs each of these 100 lines sequentially. The second applications reads these new records ordered by column time_of_write. Finally I remember last time_of_wite from which the next cycle reads new records. But often happens that in the table additionally appears record with older time_of_write after reading of that pack of records. So this record escape from reading in second aplication because time_of_write is older then remembered one. It seems that UPDATE lines in query are not executed sequentially, but randomly/in parallel. How can I solve this problem? How to make MS SQL Server to perform UPDATE lines sequentially to not loose some lines when reading?
Presumably two rows can have same time_of_write (i.e. SQL created them in the same millisecond-interval)?
If that is the case would it matter if ALL the rows had same time_of_write?
(We use same time_of_write for all operations in a single Sproc, unless there is a business-case for not doing that, so we just
DECLARE @dtNow DATETIME = GetDate()
at the top of the SProc and use that, instead of GetDate(), in all Inserts/Updates that need a Create and/or Update timestamp.
I don't know what the "cost" of getting System DateTime is? but i presume it is more than just using a predeclared @dtNow variable.
Might not work for your business case though!
Time cost of calling GETDATE() is not problem. I've tried generate value of time_of_write in application (without using GETDATE()) so query was completely defined before querying MS SQL Server. But problem was the same. Problem is nonsequential calling of UPDATEs from that packet of 100lines query.
You must specify ORDER BY on a query to get a specific order of rows. If more than one row can have the same sort key(s), those rows appear in random order, and thus could be different for one execution vs the next. That is, for example, if you are sorting by date, and four rows all had date of Jan 1, 2017, then those four rows could appear in any order and that order could change from one SELECT to the next.
Can you not, then, "remember" the most recent / latest time_of_write allocated to that data set?
I use the ORDER BY clause, of course ...