When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

• ANSI_NULLS ON
  
• ANSI_PADDING ON
  
• ANSI_WARNINGS ON
  
• CONCAT_NULL_YIELDS_NULL ON
  
• QUOTED_IDENTIFIER ON
  
• NUMERIC_ROUNDABORT OFF
  
• ARITHABORT ON
  

Note:
Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

If these options are not set appropriately, the notification is fired immediately after the SELECT statement is executed. When a notification is active, the connection that issues a command that causes a notification to fire must also have the SET options set as shown. Otherwise, the command fails with a Transact-SQL error.

When the statement is contained in a stored procedure, the ANSI_NULLS option and the QUOTED_IDENTIFIER option must be set when the stored procedure is created. For more information, see SET ANSI_NULLS (Transact-SQL) and SET QUOTED_IDENTIFIER (Transact-SQL).

In general, you can request notification for any query that can be used to create an indexed view. You can set up notifications for the following statements:

• SELECT
  For requirements and limitations specific to SELECT, see "Supported SELECT Statements" below. For more information on the SELECT statement, see SELECT (Transact-SQL).
  
• EXECUTE
  In this case, SQL Server registers a notification for the command executed rather than the EXECUTE statement itself. The command must meet the requirements and limitations for a SELECT statement. For more information on the EXECUTE statement, see EXECUTE (Transact-SQL).
  

When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.

Query notifications are supported for SELECT statements that meet the following requirements:

• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
  
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
  
• The statement may not use unnamed columns or duplicate column names.
  
• The statement must reference a base table.
  
• The statement must not reference tables with computed columns.
  
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
  
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
  
• The statement must not include PIVOT or UNPIVOT operators.
  
• The statement must not include the UNION, INTERSECT, or EXCEPT operators.
  
• The statement must not reference a view.
  
• The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
  
• The statement must not reference server global variables (@@variable_name).
  
• The statement must not reference derived tables, temporary tables, or table variables.
  
• The statement must not reference tables or views from other databases or servers.
  
• The statement must not contain subqueries, outer joins, or self-joins.
  
• The statement must not reference the large object types: text, ntext, and image.
  
• The statement must not use the CONTAINS or FREETEXT full-text predicates.
  
• The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
  
• The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
  
• The statement must not use any nondeterministic functions, including ranking and windowing functions.
  
• The statement must not contain user-defined aggregates.
  
• The statement must not reference system tables or views, including catalog views and dynamic management views.
  
• The statement must not include FOR BROWSE information.
  
• The statement must not reference a queue.
  
• The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
  
• The statement can not specify READPAST locking hint.
  
• The statement must not reference any Service Broker QUEUE.
  
• The statement must not reference synonyms.
  
• The statement must not have comparison or expression based on double/real data types.
  
• The statement must not use the TOP expression.
  

If a subscription request is made for a batch or stored procedure, a separate subscription request is made for each statement executed within the batch or stored procedure.

EXECUTE statements will not register a notification, but will flow the notification request to the executed command. If it is a batch, the context will be applied to the executed statements and the same rules described above apply.

Submitting a duplicate of an active subscription causes the existing subscription to be renewed using the new specified time-out value. A duplicate subscription is one that meets the following conditions:

• The query is submitted by the same user under the same database context.
  
• The same template, parameter values, notification ID, and delivery location are used.
  

This means that if a notification is requested for identical queries, only one notification is sent. This applies to a query duplicated in a batch, or to a query in a stored procedure that is called multiple times.

Concepts

Other Resources

Help and Information