The Mysterious Notification  June 17th, 2006  Update:: for a way
to leverage SqlDependency and Query Notifications from LINQ, see
SqlDependency based caching of LINQ Queries.  One of the most
mysterious features shipped with SQL Server 2005 seems to be the
various flavors of notifications on data change:
SqlNotificationRequest, SqlDependency and SqlCacheDependency. I
see confusion on how this features work, how to use them and how
to troubleshoot problems. Why are there three flavors of
apparently the same functionality? And how is Service Broker
involved into all of these?  Query Notifications  In reality,
there is only one feature in the SQL Server 2005 engine that
delivers notifications on subscriptions for data changes: Query
Notifications. Clients can submit a query requesting to be
notified when data was modified in a manner that would change
the query result and the server sends a notification when this
change occurs. These requests are called ‘query notification
subscriptions’. The list of notificatio