Is there a way to check if an SQL Server table (or even better, a page in that table) was modified since a certain moment? E.g. SQL differential backup uses dirty flags to know which parts of data were changed since last backup, and resets these flags after a successful backup.
Is there any way to get this functionality from MS SQL Server? I.e. if I want to cache certain aggregate values on a database table which sometimes changes, how would I know when to invalidate the cache? Or is the only way to do it to implement it programmatically and keep tract of this while writing to the database?
I am using C# .NET 4.5 to access SQL Server 2008 R2 through NHibernate.
I suggest you think about your problem in terms of application layer data caching instead of SQL Server low-level data pages. You can use SqlDependency or QueryNotification in your C# code to get notified of changes to the underlying data. Note that this requires ServericeBroker be enabled in the SQL Server database and there are some restrictions the on queries that qualify for notification.
See http://www.codeproject.com/Articles/529016/NHibernate-Second-Level-Caching-Implementation for an example of using this it with NHibernate.