When (and When Not) to Use Query Notification
Query Notification is designed for data that does not change frequently. It is also best used in server-side applications (such as ASP.NET or remoting) rather than client-side applications (such as a Windows Forms application). Remember that each request for a notification is registered in SQL Server. If you have masses of client applications each requesting a notification, this can create a resource problem on your server. Microsoft's recommendation is that for client-side applications, you limit your use of query notification to not more than ten concurrent users.

For large-scale applications, query notification can be a powerful aid in lieu of merely adding more and more servers to meet demands. Imagine a large software company that provides online software updates to thousands or even millions of users. Rather than having each user's update trigger yet another query on the server to see which components are needed, the query results can be cached and matching queries can be served directly from the cache.

For client-side applications, limit your use of query notification to no more than ten concurrent users.

On a smaller scale, drop-down lists are another typical set of data that is not updated often but requested frequently. Product lists, state lists, country lists, vendors, sales people, and more—anything that changes at a much lower frequency than it is requested—is a great candidate for notifications.

Preparing SQL Server for Query Notification
Because of SQL Server 2005's state of high security, you need to turn on some functionality in order to use Query Notification. Each database that you would like to use needs the Service Broker enabled. You can do this in T-SQL with the command

      USE mydatabase
      ALTER DATABASE mydb SET ENABLE_BROKER

Additionally, you need to grant a number of permissions in SQL Server to allow non-Administrative accounts to engage with Query Notification. These are listed in the sidebar, SQL Server Permissions for Query Notification.

SqlDependency.Start and Stop
SqlDependency and SqlCacheDependency both require that you call the static method SqlDependency.Start() prior to any notification requests. What this does is create a SqlConnection dedicated to receiving the notifications when data has changed. You need only set this up at the beginning of an application's lifetime. For example, in an ASP.NET application, the global.asax file's Application_Start event handler is a good place to do this.

The Start method is called for each connection involved in notification. Therefore, if you access multiple databases in your application, you need to call Start for each one. In the following example, there is a connection string named pubsConn for the Pubs database defined in the web.config for this application.

To shut down this connection, use SqlDependency.Stop(), also a static method.

   Sub Application_Start(ByVal sender as Object, _
      ByVal e as EventArgs)
      System.Data.SqlClient.SqlDependency.Start  _
         (System.Configuration.ConfigurationManager. _
         Connectionstrings("pubsConn").ConnectionString)
   End Sub
   Sub Application_End(ByVal sender as Object,
      ByVal e as EventArgs)
      System.Data.SqlClient.SqlDependency.Stop _
         (System.Configuration.ConfigurationManager. _
         Connectionstrings("pubsConn").ConnectionString)
   End Sub

If you watch the SQL Server profiler while Start and Stop are being called, you will see a lot of interesting information. When Start is called, the application runs a query to ensure that Service Broker is enabled and then creates a Stored Procedure to be used later that clears out the SqlDependency queue and service in the Service Broker infrastructure. Lastly, it runs a SQL Server 2005 WaitFor command that queries for entries in the Notification Service area. These are all things that you would have to do explicitly if you used the lower level SqlNotificationRequest object in ADO.NET.

Over the course of .NET 2.0's design, the underlying architecture of SqlDependency was changed from a push model (from SQL Server) to a pull model (from .NET). The reason for this was to solve some security issues that were caused by the first design. Sushil Chordia of Microsoft has a post on his blog about this change that includes a fantastic description of how things work under the covers.