I just spent some time checking out how SqlDependency works. SqlDependency is an ADO.NET 2.0 class that is a high level wrapper for detecting changes in a database and getting automatic notification back to your application. This technology is very useful for caching rarely changing data. Essentially, you register interest in a query, and if something changes that would make that query different, you get a callback to your application to let you know that you need to update your local cache.

All in all, it's a pretty mixed review right now. It's pretty stringent on the requirements, not terribly efficient, and probably not the most practical thing for a multi-tier system. However, what it does, it does well. Here is my quick list of things learned:

• You need to make sure that the DB is compatible with MSSQL2005. You can set the compatibility level in SQL Server Management Studio by selecting the Properties page of the database.
• You need to SET ENABLE_BROKER on the database, along with making sure other OPTIONS are in compliance, to allow Query Notifications to work.
• Be sure to prefix the table in the select with the schema information (e.g. select CategroyId, CategoryName from dbo.Categories). Also be sure to check out the documentation for other limitations on queries.
• The OnChange event fires once and then gets consumed, so you need to hook up the event again after it fires.
• OnChange runs in a separate thread, so be sure to update the UI properly.
• The Queues and Services in MSSQL 2005 are not being automatically deleted when you call SqlDependency.Stop. This is a known issue, and the query to delete the extra objects can be found in this discussion.
• It appears that the notification fires if ANY field in the table tied to the query is changed - even if that field is not part of the query you registered for notification. This seems like a bug to me.
• Query Notifications are not very granular in reporting WHAT changed. Right now, this essentially forces you to invalidate an entire cache as opposed to just updating individual elements that have changed.
• SqlDependency is the high-level wrapper. You can also use the low-level SqlNotificationRequest class to get more control over the process, if you so choose.
• For ASP.NET, you will most likely use the SqlCacheDependency class.
• Oracle also has support for this technology. Check out the article, Following the changes. Part I and Part II.

Other resources:

• The documentation from MSDN on Query Notifications
• A CodeProject article showing some of the permissions needed to make SqlDependency work
• A CodeProject article demonstrating some of the techniques needed to use SqlDependency
• Blog entry by Sushil Chordia, of the ADO.NET team detailing some crucial information about how SqlDependency works
• A general write up of things done to get SqlDependency working