I have a database with data that "expires" in a way -- after 10 days, the status changes from "Completed" to "Archived" and after 30 days rows still marked as "Assigned" get unassigned, for various reasons.
I'm trying to figure out the best way of handling this. (Affected row count each day would be in the thousands.)
My two routes that I have so far are:
Clean the data whenever it's queried. This would work and run in pretty close to real-time, but it would add overhead to each query -- basically, we'd query, clean up the data, then run the normal query. That seems pretty inefficient for something that really only needs to be done once per day.
Run a separate process. Like a Windows service or something, that cleans everything up. It would run once per day at midnight (which is when the app is quiet). It's not real-time, but we're talking a granularity of a day so that's probably fine. And we could add more tasks to the process as time goes on.
My question is: are these really the two main ways of accomplishing this, or is there another method that I'm not thinking of? Route #2 seems the clearly better one for my purposes, but I had a coworker say that it sounds like a hack to him so I thought I'd throw this out here and see if anyone had a better idea.
I suppose I could use database processes directly on SQL Server, but I don't know if that's really materially different from a separate process (both involve writing a new routine and scheduling it).