VS2005/SQL Server 2005 RTM Query Notification Rules

The rules for using Query Notification have finally settled after evolving through all of the betas and ctps. Here they are copied and pasted directly from the msdn help files:

Applications that use query notification features need to take into account the following special considerations.

Valid Queries

Query notifications only support certain Transact-SQL statements.

First, to support notifications, queries must not contain:

  • Derived tables.

  • Rowset functions.

  • The UNION operator.

  • Subqueries.

  • Outer or self-joins.

  • The TOP clause.

  • The DISTINCT keyword.

  • A COUNT(*) aggregate.

  • AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregates.

  • User-defined aggregates.

  • A SUM function that references a nullable expression.

  • The full-text predicates CONTAINS or FREETEXT.

  • A COMPUTE or COMPUTE BY clause.

  • Aggregate expressions if GROUP BY is not specified in a select list. If GROUP BY is specified, the select list must contain a COUNT_BIG(*) expression, and cannot specify HAVING, CUBE, or ROLLUP.

  • An INTO clause.

  • Conditions that will preclude results from changing (e.g. WHERE 1=0).

  • FOR BROWSE (or be running with SET NO_BROWSETABLE ON).

  • A READPAST locking hint.

Second, queries must not reference:

  • Temporal tables or table variables.

  • Tables or views from other databases or servers.

  • Any other views or table-valued functions.

  • Any system tables or views.

  • Any nondeterministic function, including ranking and windowing functions.

  • Any server global variables.

  • Any Service Broker queue.

  • Synonyms.

Finally, queries must reference a base table or view.

Rapid Updates

An application that uses Query Notifications must take into consideration cases where a notification occurs immediately. When data is changed on the server, a notification message will be sent to the appropriate Service Broker queue. Applications need to reregister to receive additional notifications. Therefore, if a data set is updated quickly by multiple applications, an application could receive a notification, retrieve the data, and then get another update notification almost immediately after the cache has been refreshed. Applications that use Query Notifications must be written to take this case into account. If an application uses data that is constantly updated, another strategy for caching data may be more appropriate.

Transactions

If multiple modifications are made to a set of data with a registered notification request, and those changes occur within a transaction, only a single notification event will be sent.

Service Account for SQL Server

An application will not receive notifications from an instance of SQL Server that uses the Local System account as the service account.

Posted from BLInk!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

2 thoughts on “VS2005/SQL Server 2005 RTM Query Notification Rules

  1. Wow…No comments.

    I’m interested in all the ways besides using QN that can be used to provide updating across clients.

  2. Hi Tamus

    Look for an article (circa 2004 maybe?) by Jeff PRosise in MSDN Magazine (msdn.microsoft.com/magazine) a trick for using ADO.NET to do this.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.