Understanding When Query Notifications Occur

The concept behind query notifications is that the application can rely on cached data until a notification message arrives. If SQL Server can no longer guarantee that the cached data is reliable, SQL Server sends a notification message. SQL Server sends a query notification for a subscription when one of the following events occurs:

• Rows contained in the query results may have changed.

• The subscription expires.

• The server restarts.

• The query notification subscription could not be created (for example, the SELECT statement does not conform to the requirements specified in Creating a Query for Notification.

• The server is heavily loaded.

• Objects that the subscription depends on are dropped or modified.

Notice that SQL Server may produce a query notification in response to events that do not change the data, or in response to a change that does not actually affect the results of the query. For example, when an UPDATE statement changes one of the rows returned by the query, the notification may fire even if the update to the row did not change the columns in the query results.. Query notifications are designed to support the overall goal of improving performance for applications that cache data. When the server is heavily loaded, SQL Server may produce a query notification message for the subscription rather than performing the work of determining whether the results of the query have changed.

Notification subscription occurs before the Database Engine runs each statement in the subscription request. Therefore, if the command contains both a query and statements that changes the data returned by the query, the application can receive a notification message before the command that contains the subscription request completes.

Each subscription has a specified minimum lifetime. After the minimum lifetime, SQL Server removes the subscription and creates a notification message. The message informs the application that the notification is no longer active, and SQL Server is no longer tracking changes to the query.

When SQL Server starts, the server automatically creates query notification messages for all query notification subscriptions in the database. This allows applications to immediately refresh cached data, and allows SQL Server to start without processing the query in each notification. When you drop a database, the Database Engine produces query notification messages for all subscriptions registered in that database.

If a notification subscription request is submitted with a query that does not meet the requirements for query notification, the database immediately produces a query notification message. This message informs the application that the query did not meet the requirements for a subscription. Further, because SQL Server cannot track changes to the query, the application should consider the results of the query to be out of date immediately.

When a command produces a notification message, the process of creating and sending a notification message occurs as part of a statement, within the transaction that produces the notification message. If the Database Engine cannot successfully create and send the notification message, the command fails and the failure is logged in the SQL Server error log. Notice that the Database Engine removes the notification subscription when the notification message is produced, regardless of whether the Database Engine can successfully produce the notification message.