Alternative to polling MSSQL table

Question

I have a MSSQL table that contains scheduled tasks that my Windows Service should process based off a timestamp and I was wondering what alternatives I have to polling the table like this

SELECT *
FROM mydb
WHERE SYSUTCDATE() >= timestamp

I'd probably need to poll the table at least every 5 seconds. Basically I want my Windows Service to process the data at the time set by the timestamp in the table.

To me this does not seem like the most effective way. I've already looked into DML & CLR triggers and I don't think they'll work as they'll fire when the data changes and not when the timestamp has passed. Thoughts?

---

Update 2:

I've realized that calling it "scheduled tasks" was a poor choice of wording so I'll try to describe it in more detail.

The goal of this project is send phone notifications to people depending on our business logic. One scenario is that multiple people should be phoned at specific times based on an internal event. The same person can be called multiple times depending on how the phone call is answered. Thus to simplify things and remove the complexity and overhead of managing the status of each phone call I thought it would be a good idea to preschedule each phone call by having it as an entry in a table. When the notifications should be stopped, the pending phone calls are deleted from the table. This would keep the design of the Windows service very simple. All it would do is send the notification based on it's timestamp in the table.

---

Update 1:

Message Queue

I haven't figured out how the sender will put messages onto the queue at the proper time.

SqlDependency

I'm having an issue using the example code from Detecting Changes with SqlDependency. For some reason the OnChange event only gets fired initially, nothing happens later.

Update: I don't think the SqlDependency will work as the data in the table will not change to make the trigger fire.

void Initialization()
{
   // Create a dependency connection.
   SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
   // Assume connection is an open SqlConnection.

   // Create a new SqlCommand object.
   using (SqlCommand command=new SqlCommand(
      "SELECT timestamp,othercolumn FROM mydb WHERE SYSUTCDATE() >= timestamp", 
       connection))
   {

   // Create a dependency and associate it with the SqlCommand.
   SqlDependency dependency=new SqlDependency(command);
   // Maintain the refence in a class member.

   // Subscribe to the SqlDependency event.
   dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

   // Execute the command.
   using (SqlDataReader reader = command.ExecuteReader())
   {
      // Process the DataReader.
   }
}

Answer 1

OK, first of all, consider not doing this at all. Shoving off all useful work to periodic tasks that are configured in a database is a fragile design that is prone to breaking easily when someone misconfigures things (easy to do since you need pretty advanced triggers to check for schedule consistency), and it also tends to create a system that is incomprehensible when the tasks actually have hidden dependencies (if A hasn't run some time before B, stuff breaks, that sort of thing). Source: personal experience with three such systems in three different companies and three different platforms/technologies and somehow they all suffered from the same problems, so apparently it's a thing. Consider just writing out the things you want to schedule as plain old code, with a configuration file. Sure, it won't be as generic, but the people who have to maintain it will thank you, especially as their needs become more complex.

SqlDependency is rather fickle and not easy to use well even if you do have a query that's supported. In your case, as you've noticed, it doesn't work because the database engine will not post a notification unless data actually changes -- it doesn't matter that the results of the query will change as time passes. As Nick has pointed out, polling a database every 5 seconds is typically just fine. This produces negligible load, provided you have created an index on mydb.timestamp (and that you create this is rather vital, since performing a table scan every 5 seconds is not OK).

The only objection is latency: if any updates to the schedule must happen sooner than once every 5 seconds, polling isn't good enough. In this case, you can use Service Broker and post a notification to the queue the instant something changes (possibly from a trigger). In fact, SqlDependency uses the same approach under the covers, so you can create a dependency on SELECT * FROM table to get a notification whenever anything changes in the table, and then perform the actual query to get what you need (possibly finding out there's nothing). Beware, though: getting the code for this correct without getting confused by multiple rapid updates or the connection breaking is not trivial and probably not worth it, as opposed to just reloading periodically.