Planning for Notifications
To use query notifications effectively, you should consider whether your application can benefit from query notifications, whether the queries that your application uses support notifications, and the strategy your application will use for subscribing to and receiving notifications.
Query notifications provide a convenient way to reduce roundtrips to the database if the data in the query changes relatively infrequently, if the application does not require an instantaneous update when the data changes, and if the query meets the requirements and restrictions outlined in Creating a Query for Notification. Many Web-based applications meet these criteria, and these applications can take advantage of query notifications.
Not every scenario benefits from query notifications. Query notifications are useful in situations where an application reads data from the database frequently, but where updates to the data are relatively infrequent. For example, an online catalog application will be viewed more frequently than the catalog is updated. For an online shopping cart, however, the content of a particular could be updated quite frequently, so query notifications provide less benefit.
Query notifications are more efficient when an application issues queries that share a common structure and vary only in the values of the parameters. For example:
SELECT ProductNumber, Name FROM Production.Product WHERE ListPrice < 300
SELECT ProductNumber, Name FROM Production.Product WHERE ListPrice < 500
In this case, query notification subscriptions for both notifications share the same internal template, requiring less overhead in SQL Server than two notifications with a different query structure. Notice, however, that the parameters in the queries are preserved. Even though the queries share a template, adding an item with a ListPrice of 350 causes notification on the second query, but not the first.
When query notifications are active on a table, updates to the table are more expensive. The Database Engine performs extra work to check subscriptions and, if necessary, generate notifications. Reusing internal templates helps to minimize the overhead per subscription. Therefore, you should use query notifications only for applications that submit queries with a similar structure. An application that submits queries with different structures should not use query notifications.
For example, an application that shows catalog items in a given price range submits queries with the same structure. In this case, the Database Engine can reuse the internal template for each query, and query notifications may improve performance. However, an application that allows ad hoc reporting submits queries with varying structure. In this case, the application should not use query notifications.
The Database Engine maintains an internal template as long as it is used by at least one registered subscription. The Database Engine limits the number of different internal templates on a specific table. Once this limit is reached, the Database Engine does not register subscriptions that would cause a new template to be created. Instead, the Database Engine immediately generates a subscription message indicating that the subscription could not be registered.