The ability to have SQL Server automatically invalidate caches is quite interesting as well. For SQL Server 2000, it needs to periodically poll the database, and only checks to see if a table has changed. SQL Server 2005 will ping the cache and tell it to invalidate, and also supports row level invalidations. To be honest, while this seems like an incredibly cool and useful feature, it also creates a massive amount of vendor lock in. You may be better served by having your application’s application layer handle the caching using key caching, and invalidate caches when it calls non-retrieval portions of the data access layer. While that may not be quite as spiffy as the automatic notification of row-level changes, you can do something nearly as good with a well designed database: use the objects in the cache to hold onto the main record ID of records (such as the record ID for the employee table) and cascade invalidation for that main record in the database to the other associated, cached items (such as the payroll table data for that employee). While this may be a good bit of effort, it may pay off to keep your application vendor neutral.
More...
Some Notes on ASP.Net’s Caching System 
