Using Query Notifications
SQL Server 2005 introduced query notifications, new functionality that allows an application to request a notification from SQL Server when the results of a query change. Query notifications allow programmers to design applications that query the database only when there is a change to information that the application has previously retrieved.
For example, an online catalog application may cache the results of a query that lists the items that are on sale. The application presents the catalog based on the cached data. When the list of sale items changes, an event handler in the application receives the notification event, and discards the cached data. The next time a customer requests the list of sale items, the application queries the database for the current data and renews the notification subscription.
Applications can take advantage of query notifications to reduce round trips to the database. Instead of writing code that periodically re-executes a query to maintain current results, developers can design applications that are automatically notified when the results for the query may be out of date. With query notifications, the application issues a command that contains a query and a request for notification. The application caches the results of the query or dynamic content generated from the query results. When the application receives the query notification, the application clears the cached content. The application then re-issues the query and notification request when the application needs the updated query results.
The Database Engine uses notification subscriptions to track requests for query notifications. When a command contains a notification request, the database registers the request as a notification subscription and then executes the command.
The Database Engine uses Service Broker to deliver notification messages. Therefore, Service Broker must be active in the database where the application requests the subscription. The query notification functionality does not require or use Notification Services. Query notifications are independent of event notifications.
To receive a notification message in a database other than the database that contains the subscription, the database that contains the subscription must have the TRUSTWORTHY property set to ON. For more information, see ALTER DATABASE (Transact-SQL).
See Also