How can I get a list of modified records from a SQL Server database?

Question

I am currently in the process of revamping my company's management system to run a little more lean in terms of network traffic. Right now I'm trying to figure out an effective way to query only the records that have been modified (by any user) since the last time I asked.

When the application starts it loads the job information and caches it locally like the following: SELECT * FROM jobs.

I am writing out the date/time a record was modified ala UPDATE jobs SET Widgets=@Widgets, LastModified=GetDate() WHERE JobID=@JobID.

When any user requests the list of jobs I query all records that have been modified since the last time I requested the list like the following: SELECT * FROM jobs WHERE LastModified>=@LastRequested and store the date/time of the request to pass in as @LastRequest when the user asks again. In theory this will return only the records that have been modified since the last request.

The issue I'm running into is when the user's date/time is not quite in sync with the server's date/time and also of server load when querying an un-indexed date/time column. Is there a more effective system then querying date/time information?

Answer 1

The easiest solution seems to settle on one time as leading.

One way would be to settle on the server time. After updating the row, store the value returned by select LastModified where JobID = @JobID on the client side. That way, the client can effectively query using only the server time as reference.

Answer 2

I don't know that I would rely on Date-Time since it is external to SQL Server.

If you have an Identity column, I would use that column in a table UserId, LastQueryDateTime, LastIdRetrieved

Every time you query the base table, insert new row for user (or update if exists) the max id into this table. Also, the query should read the row from this table to get the LastIdRetrieved and use that in the where clause.

All this could be eliminated if all of your code chooses to insert GetDate() from SQL Server instead of from the client machines, but that task is pretty labor intensive.

Answer 3

Use an update sequence number (USN) much like Active Directory and DNS use to keep track of the objects that have changed since their last replication. Pick a number to start with, and each time a record in the Jobs table is inserted or modified, write the most recent USN. Keep track of the USN when the last Select query was executed, and you then always know what records were altered since the last query. For example...

Set LastQryUSN = 100

Update Jobs Set USN=101, ...

Update Jobs Set USN=102, ...

Insert Jobs (USN, ...) Values (103, ...)

Select * From Jobs Where USN > LastQryUSN

Set LastQryUSN = 103

Update Jobs Set USN=104

Insert Jobs (USN, ...) Values (105, ...)

Select * From Jobs Where USN > LastQryUSN

Set LastQryUSN = 105

... and so on

Answer 4

When you get the Jobs, get the server time too:

DECLARE @now DATETIME = GETUTCDATE();

SELECT @now AS [ServerTime], * FROM Jobs WHERE Modified >= @LastModified;

First time you pass in a minimum date as @LastModified. On each subsequent call, you pass in the ServerTime returned last call. This way the client time is taken out of the equation.

The answer to the server load is, I hope, obvious: add an index on Modified column.

And one more adice: never use local time, not even on server. Always use UTC times, and store UTC time in Modified. As it is right now, your program is completely screwed two times a year, when the daylight savings time changes are set in or when they are removed.

Answer 5

Current SQL Server has change tracking you can use for exactly that. Just enable change tracking on the tables you want to track.