SQL Server Yukon and SqlCacheDependency

The SqlCacheDependency class works differently according to the underlying version of SQL Server. If it works with SQL Server 7.0 or 2000, it can only detect changes on a table. Any change to the table invalidates a Cache item implicitly bound to it. If you work with SQL Server Yukon, then things are a bit different.

In SQL Server Yukon, you can exercise a finer control over the monitored data. In particular, you can detect changes only on the output generated by a particular, cross-table query. This capability descends from a new Yukon feature--command notification.

Whenever something happens to the involved tables that modifies the output of the command, a notification is automatically sent to caller. The caller this time is just the SqlCacheDependency class. Internally, the SqlCacheDependency class handles the notification event and bubbles it up to the Cache object through a new method defined on the new Whidbey's CacheDependency class.

All in all, I find that the custom CacheDependency mechanism is quite complex, though workable.

In ASP.NET 1.x it is tailor-made for files and other cache keys and works great. But that mechanism wasn't designed to be extensible.

In ASP.NET 2.0, the whole thing can be extended but it is better if your items are dependent on a resource that can send asynchronous notifications (like the Yukon's command notification or a message queue). Otherwise, polling is the only way to go and can be tricky, if not boring.

However, this is just my early feeling about the technology.