Query Notification in SQL Server

Introduction

SQL Server 2005 introduced Query Notification. Query Notification allows an application to request a notification from SQL Server when the Query result is changed. Query Notification helps us to reduce round trips to the database. Previously we needed to check the data periodically to maintain query results, using Query Notification the programmer can design such an application that is automatically notified when the query result is changed.

The Database Engine uses a Service Broker to deliver the notification messages and use notification subscriptions to track requests for Query Notification. Query Notification does not use Notification Services and also they are independent of event notifications. The Query Notification feature is very useful for the applications that provide cache data such as web application and need to notify when the data is changed in the database.

The Query Notification functionality is developed on top of the change detection mechanism of the database. To do this database engine, maintain the indexed views, so the requirements and restrictions of Query used in Query Notification are the same as an indexed view.

Some points must be considered while writing SQL Query for Query Notification.

All the column names must be explicitly defined in a SQL query (do not use (*) to retrieve the columns of the database) as in the following:

SELECT Col1, Col2, Col3 FROM dbo.Table1
The SQL query does not contain unnamed or duplicate columns.
The Query table must have a schema name
The SQL query must not reference a table with a computed column.
The SQL Query must not contain PIVOT or UNPIVOT operators.
The SQL Query must not contain UNION, INTERSECT, EXCEPT, DISTINCT, COMPUTE or COMPUTE BY, or INTO operators.
The SQL must not reference to the view.
The SQL Query must not reference a derived table, table variable or temporary table.
The SQL query must not contain sub query, outer joins or self-join.
The SQL query must not reference the large object such as text, ntext or image.
The SQL query must not use any full text predicates such as CONTAINS and FREETEXT.
The SQL query must not use any aggregate function such as AVG, COUNT, MAX, MIN and so on.
The SQL query must not reference any server global variables.
The SQL query must not use a table or a view from another database or server.
The SQL Query must not use the TOP expression.
The SQL query must not reference any Service broker Queue.
The SQL query must not contain any rowset functions such as OPENROWSET and OPENQUERY.
The SQL query must not use any user defined aggregates.
The SQL query must not use any system tables or views.
The Query must not run under READ_UNCOMMITTED or SNAPSHOT isolation levels.
The Option Setting and isolation level also plays an important role in Query Notification. When a SQL SELECT query is executed under the Query Notification, the request must be under ANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, CONCAT_NULL_YIELDS_NULL ON, QUOTED_IDENTIFIER ON, NUMERIC_ROUNDABORT OFF, and ARITHABORT ON Options.

If the options and isolation level are not set appropriately then notification is fired immediately after the SELECT statement is executed.
If the Stored Procedure is used as a notification query then the ANSI_NULLS and the QUOTED_IDENTIFIER option must be set when the Stored Procedure is created.

Prerequisites to Create SQL Query Notification Request

Enable Service Broker

A Service Broker is used to deliver notification message to the query requestor, so the Service Broker must be enabled for the database. Please refer to Service-broker-in-sql-server.

If Query Notification is a subscription done from an application other than the database, the TRUSTWORTHY property must be set to ON for the database that contains the subscription.
Query Notification requires a few permissions for the database. A notification subscription is owned by the database principal that is used to execute the command that registered the subscription.

USE DataBaseName
GRANT SUBSCRIBE QUERY NOTIFICATIONSTO database_principal

To receive the notification, the subscriber user must have RECEIVE permissions and also SEND permission.

GRANT RECEIVEON Table TO UserName(login)
GRANT SENDON SERVICE:://the serviceName to login
SQL Query that is used for Query Notification must fulfill the above mentioned points.

The Query Notification message contains XML. Every notification message contains a date and time of when the notification was created and the reason for the notification. There are the main two attributes in the notification message: The QueryNotification attribute identifies the reason for the message and the Message attribute contains the notification id for the subscription. The Query Notification is type "http://schemas.microsoft.com/SQL/Notifications/QueryNotification" and the Message is part of "http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification".

The following is a sample Query Notification message:

<qn:QueryNotification
xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
Type="change" Source="data" Info="insert">
<qn:Message>http://mysite.microsoft.com/catalog.aspx?Category=Cars</qn:Message>
</qn:QueryNotification>

Please refer Query Notification Message for more information.

The dynamic management view sys.dm_qn_subscriptions contains information about the active notification subscription. Using this dynamic management, we can check for an active notification subscription for a specific database on the server.

Summary

Query Notification provides a way to reduce the roundtrip to the database if the data changes within a query vary infrequently. Query Notification is very useful in situations where an application reads the data from a database very frequently but the data changes very infrequently. Query Notifications work fine when the number of notifications are light to moderate and the application does not require an immediate notification response time if the data changes. Query Notification might not be a good choice for the application in which notification is received within a sub second of time or the volume of the notification is very high.