We recommend using Visual Studio 2017

Query Notifications in ADO.NET 2.0

 

Bob Beauchemin
DevelopMentor

April 2005
Updated June 2005

Applies to:
   ADO.NET 2.0

Summary: Learn how to use the new notification technologies in ADO.NET 2.0 and SQL Server 2005 to handle ad-hoc data refreshes. (14 printed pages)

Contents

Introduction
SqlDependency Provides a Solution for Caching
Query Notifications in SQL Server 2005
Dispatching a Notification to an End User or Cache
Using Query Notifications from a Database Client
Using SqlDependency
Using SqlNotificationRequest
Using SqlCacheDependency in ASP.NET
Eager Notifications
When Not to Use Notifications: A Cautionary Tale
Conclusion

Introduction

Any non-trivial relational database application is bound to have a lot of lookup tables. If you code graphical user interfaces as a specialty, you know these as the lists that populate the drop-down list boxes. I categorize lookup tables into two types: read-only tables and read-mostly tables. The difference is what can cause those tables to change. I think of a table as read-only if it takes a staff meeting or user meeting to change them. A good example is a table that contains categories of a company's products. That table is not going to change unless the company launches a new product or the company is reorganized. Read-mostly tables are lists that are relatively constant, but can be changed by end users. These usually are presented in combo boxes rather than drop-down lists. An example of a read-mostly table would be a term-of-greeting table. Your application designers can always think of the most common ones, like Ms., Mr., Mrs., and Dr., but there's always the user who has a title you've never thought of and wants to add it in. As an example of how common this is, the last medium-sized product I worked on had a nice third normal form relational database that contained 350-400 tables. I'd estimate that about 250 were read-only or read-mostly tables.

In the traditional Web application (which is the quintessential example of a three-tier application), you'd like to cache these types of tables as much as possible. This not only decreases the number of roundtrips to the database, but also decreases the query load on the database, making it more responsive for use cases like new orders. Read-only tables are easy to cache; you always keep the table in the cache and give a database administrator (DBA) a way to reload the cache on the rare occasion where she has to reload the table. Meetings that change the basic database structure and content are a rare occurrence in your organization, I hope. Refreshing read-mostly lookup tables in a middle-tier cache is a bit more problematic. Refreshing the cache infrequently on a schedule doesn't produce the behavior you want; users don't see each other's changes immediately. A support person could add a new item using a different application, send an instant messenger message to a friend who tries to use it, but the friend's list of choices doesn't include the new entry. Worse yet, if the second user now tries to re-add the "missing list entry" he receives a database error indicating the item already exists. Caching read-mostly tables usually isn't done if they have more than one "point-of-update" because of problems like this.

In the past, programmers have resorted to handcrafted solutions using message queuing, triggers that write to files, or out-of-band protocols to notify the cache when someone outside of the application has updated a read-mostly table. These "signaling" solutions merely notify the cache that a row has been added or changed, indicating that the cache must be refreshed. Notifying the cache about which specific row has changed or been added is a different problem, it's the province of distributed database and transactional or merge replication. In the low-overhead signaling solution, once the program gets a "cache invalid" message, it just refreshes the entire cache.

SqlDependency Provides a Solution for Caching

If you are using SQL Server 2005 and ADO.NET 2.0 there is now a signaling solution built in to the SqlClient data provider and the database called Query Notifications. At last, a built-in, easy-to-use solution to this common problem! Query Notifications are also directly supported with a built-in feature in ASP.NET 2.0. The ASP.NET Cache can register for notifications and the notifications can even be used in conjunction with the page and page-fragment caching that ASP.NET uses.

The infrastructure that accomplishes this useful function is comprised of the SQL Server 2005 Query Engine, the SQL Server Service Broker, a system stored procedure, sp_DispatcherProc, the ADO.NET SqlNotification (System.Data.Sql.SqlNotificationRequest), and SqlDependency (System.Data.SqlClient.SqlDependency) classes, and the ASP.NET Cache (System.Web.Caching.Cache) class. In brief, it works like this:

  1. Each ADO.NET SqlCommand contains a Notification property that represents a request for notification. When the SqlCommand is executed, the presence of a Notification property causes a network protocol (TDS) packet that indicates a request for notification to be appended to the request.
  2. SQL Server registers a subscription for the requested notification with the query notification infrastructure and executes the command.
  3. SQL Server "watches" the SQL DML statements for anything that could cause the originally returned rowset to change. When a change occurs, a message is sent to a Service Broker SERVICE.
  4. This message can either:
    1. Cause a notification to fire back to the client that registered.
    2. Sit in the Service Broker's service QUEUE available for custom processing by advanced clients.

ms379594.sql2k_querynotification_01(en-US,VS.80).gif

Figure 1. High-level overview of Query Notifications

The ASP.NET SqlCacheDependency (System.Web.Caching.SqlCacheDependency) class and OutputCache directive use SqlDependency to use the automatic notification capability. ADO.NET clients that require more control can use SqlNotificationRequest and process the Service Broker queue manually, implementing whatever custom semantics they'd like. Although a detailed explanation of Service Broker is beyond the scope of this article, our sample book chapter from A First Look at SQL Server 2005 for Developers and Roger Wolter's article, A First Look at SQL Server 2005 Service Broker, can get you off to a good start.

Before proceeding, it's important to clarify that each SqlNotificationRequest or SqlDependency gets a single notification message when the rowset changes. The message is identical whether the change is caused by a database INSERT, a DELETE statement that deletes one or more rows, or an UPDATE that updates one or more rows. The notification does not contain any information about which rows have changed, or how many have changed. When the cache object or user application receives the single change message, it has one choice: refresh the entire rowset and re-register for the Notification. You don't get multiple messages, and after the single message fires, the user's subscription in the database is gone. The Query Notification framework also works on the premise that it's better to be notified of more events than to not be notified at all. Notifications are sent not only when the rowset is changed, but also when a table that participates in the rowset is dropped or altered, when the database is recycled, or for other reasons. The cache or program's response is usually the same, regardless: refresh the cached data and re-register for notification.

Now that you've got the general semantics involved, we'll look at how this works in detail from three perspectives:

  1. How Query Notifications are implemented in SQL Server and how the optional dispatcher works.
  2. How SqlClient's SqlDependency and SqlNotificationRequest work on the client/middle tier.
  3. How ASP.NET 2.0 supports SqlDependency.

Query Notifications in SQL Server 2005

At the server level, SQL Server handles queries from clients in batches. Each query (think of the query as the SqlCommand.CommandText property) can contain only one batch, although a batch can contain multiple T-SQL statements. A SqlCommand can also be used to execute a stored procedure or user-defined function, which can contain multiple T-SQL statements. In SQL Server 2005, a query from a client can also contain three additional pieces of information: the name of a Service Broker service to deliver notifications to, a notification identifier (which is a string), and a notification timeout. If these three pieces of information are present in the query request, and the request contains SELECT or EXECUTE statements, SQL Server will "watch" any rowsets produced by the query for changes made by other SQL Server sessions. If there are multiple rowsets produced, say, in a stored procedure execution, SQL Server will "watch" all of the rowsets.

So what do I mean by "watch" rowsets, and how does SQL Server accomplish this? Detecting changes to a rowset is part of the SQL Server engine and uses a mechanism that has been around since SQL Server 2000—change detection for indexed VIEW synchronization. In SQL Server 2000, Microsoft introduced the concept of indexed views. A view in SQL Server consists of a query against columns in one or more tables. A view has a name that can be used like a table name. For example:

CREATE VIEW WestCoastAuthors
AS 
SELECT * FROM authors
  WHERE state IN ('CA', 'WA', 'OR')

You can now use the view as though it where a table in a query like

SELECT au_id, au_lname FROM WestCoastAuthors
  WHERE au_lname LIKE 'S%'

Most programmers are familiar with views, but may not be familiar with indexed views. In a non-indexed VIEW, the VIEW data is not stored in the database as a separate copy; each time the VIEW is used, the underlying query is executed. So, in the example above, the query to get the WestCoastAuthors rowset will be executed and it will include a predicate to pull out the particular WestCoastAuthors that we want. An indexed view stores a copy of the data, so if we make WestCoastAuthors an indexed view, we have two copies of these authors' data. You can now update the data through two paths, either through the indexed VIEW or through the original TABLE. Therefore, SQL Server has to detect changes in both physical data stores to apply the changes to the other one. This change detection mechanism is the same mechanism that the engine uses when Query Notifications are used.

Because of the way change detection is implemented, not all VIEWs can be indexed. The limitations that apply to indexed views also apply to queries that can be used for Query Notifications. For example, the WestCoastAuthors VIEW cannot be indexed the way it is written. To index it, the VIEW definition must use two-part names and name all of the rowset columns explicitly. So, let's change the VIEW in order to index it.

CREATE VIEW WestCoastAuthors
WITH SCHEMABINDING
AS
SELECT au_id, au_lname, au_fname, address, city, state, zip, phone
  FROM dbo.authors
  WHERE state in ('CA', 'WA', 'OR')

Only queries that go by the indexed view rules can be used with notifications. Note that although the same mechanism is used for determining if the result of a query has changed, Query Notifications do not cause SQL Server to make a copy of the data, as indexed views do. There is a list of the rules for indexed views that is quite extensive; you can find it in the SQL Server 2005 Books Online. If a query is submitted with a notification request and it does not go by the rules, SQL Server immediately posts a notification with the reason, "Invalid Query." But where does it "post the notification"?

SQL Server 2005 uses the Service Broker feature to post notifications. Service Broker is asynchronous queuing functionality that is built in to SQL Server. Query Notifications use Service Broker SERVICEs. A SERVICE in this case is a destination for asynchronous messages; the messages can be required to follow a specific set of rules known as a CONTRACT. A Service Broker SERVICE is always associated with a QUEUE that is the physical message destination. The CONTRACT for Query Notifications is built in to SQL Server, its name is http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification.

Note   Although the SQL Server object name of the contract happens to be a URL, it implies nothing about location. It's just an object name, like dbo.authors is the name of a TABLE.

Putting this all together, the destination for Query Notification messages can be any SERVICE that supports the appropriate CONTRACT. The SQL DDL to define such as a service would look like this:

CREATE QUEUE mynotificationqueue
CREATE SERVICE myservice ON QUEUE mynotificationqueue
 ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
GO

You could now use the myservice SERVICE as a destination in a query notification request. SQL Server sends a notification by sending a message to the SERVICE. You can use your own SERVICE or have SQL Server use a built-in service in the MSDB database. If you use your own SERVICE, you must write code to read the messages and process them. If you use the built-in service in MSDB, there is pre-written code that delivers the message. I'll come back to this later.

Because query notifications use Service Broker, there are some additional requirements.

  1. Service Broker must be enabled in the database where the notification query runs. In Beta 2, it is not enabled by default in the AdventureWorks sample database, but can be enabled with an "ALTER DATABASE SET ENABLE_BROKER" DDL statement.
  2. The user submitting the query must have the permission to subscribe to query notifications. This is done on a per database basis; the following DDL would give the user 'bob' permission to subscribe in the current database:
          GRANT SUBSCRIBE QUERY NOTIFICATIONS TO bob
    

Dispatching a Notification to an End User or Cache

So at this point, we've submitted the correct kind of query batch with a notification request to SQL Server. SQL Server has put a watch on the rowset, and when anyone changes the rowset, a message will be sent to the SERVICE of our choice. What now? You can write custom code that takes care of reading the messages and performing whatever logic you'd like when the notifications occur. Or you can have the built-in dispatcher take care of it for you. So let's look at the dispatcher.

Unless you specify a custom SERVICE, query notifications use a default SERVICE in the MSDB database named http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService. When messages arrive in this SERVICE's QUEUE, they are automatically processed by a stored procedure associated with the QUEUE named sp_DispatcherProc. An interesting point is that this procedure uses code written in .NET, so loading the .NET common language runtime (CLR) must be enabled in the SQL Server 2005 instance for automatic query notification delivery to work. (Loading the .NET CLR can be enabled or disabled on a per-SQL Server instance basis).

When a query notification message arrives, sp_DispatcherProc (which I'll call "the dispatcher" from now on) looks at the SqlDependency notifications queue's list of query notification subscriptions and sends a message to each subscriber. Note that when using the dispatcher, the server notifies the client that the data has changed. This is good for two reasons: the client doesn't have to poll for notifications and the client doesn't have to keep a connection to SQL Server open to receive the notifications. The dispatcher sends this notification to each subscriber using either HTTP protocol or TCP and a private protocol. The server-to-client communication can optionally be authenticated. After the notification is delivered, the subscription is deleted from the active subscriptions list. Remember that each client subscription only gets one notification; it's up to the client to resubmit the query and re-subscribe.

Using Query Notifications from a Database Client

Now that we know all the internal plumbing, let's write an ADO.NET client that uses it. Why all this explanation before we write some relatively simple client-side code? Although the code is fairly easy to write, you have to remember to go by the rules. The most common problems are submitting an invalid query for notifications and forgetting to set up the Service Broker and user permissions. This has caused frustration with this powerful feature, even giving some beta testers the impression that it wasn't working. A little prep work and research goes a long way. Finally, it was good to do internals first because we're going to specify properties like Service Broker SERVICEs and protocols for the dispatcher, and by now you know what these terms refer to.

You can write a query notification client in ADO.NET as we'll be doing, using OLE DB, or even using the new HTTP Web service client, but a point to remember is that query notifications are only available through client-side code. You cannot use this feature with T-SQL directly or with SQLCLR procedural code that uses the SqlServer data provider to talk to SQL Server.

The System.Data.dll assembly contains two classes that you can use: SqlDependency and SqlNotificationRequest. You use SqlDependency when you want automatic notification using the dispatcher. You use SqlNotificationRequest when you want to process the notification messages yourself. We'll look at an example of each one.

Using SqlDependency

The steps to use SqlDependency are simple. First, create a SqlCommand that contains the SQL statements that you want query notifications for. Associate the SqlCommand with a SqlDependency. Then register an event handler for the SqlDependency OnChanged event. Next, execute the SqlCommand. You can process the DataReader, close the DataReader, and even close the associated SqlConnection; the dispatcher will notify you when there is a change in your rowset. The events for this object are fired in different threads; this means that you have to be ready to deal with situations where the event is fired while your code is still running. You may even be still processing the results from your batch when the event is fired. Here's the code:

using System;
using System.Data;
using System.Data.SqlClient;
static void Main(string[] args)
{
  string connstring = GetConnectionStringFromConfig();
  using (SqlConnection conn = new SqlConnection(connstring))
  using (SqlCommand cmd = 
   // 2-part table names, no "SELECT * FROM ..."
   new SqlCommand("SELECT au_id, au_lname FROM dbo.authors", conn))
 {
  try 
  {
    // create dependency associated with cmd
    SqlDependency depend = new SqlDependency(cmd);
    // register handler
    depend.OnChanged += new OnChangedEventHandler(MyOnChanged);
    conn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    // process DataReader
    while (rdr.Read())
    Console.WriteLine(rdr[0]);
    rdr.Close();
    // Wait for invalidation to come through
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
  }
  catch (Exception e)
   { Console.WriteLine(e.Message); }
 }
}
static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
  Console.WriteLine("result has changed");
  Console.WriteLine("Source " + e.Source);
  Console.WriteLine("Type " + e.Type);
  Console.WriteLine("Info " + e.Info);
}

You can write the same code in Visual Basic .NET using the familiar WithEvents keyword along with the SqlDependency. Note that this program will only get and process a single OnChanged event, no matter how many times the underlying results change. For any non-trivial usage, what we really want to do when we're notified is resubmit the command with a fresh notification and use its results to refresh the cache with the new data. If we take our code in Main() in the example above and move it to a routine named, our code might look something like this:

static void Main(string[] args)
{
    GetAndProcessData(); 
    UpdateCache();
    // wait for user to end program
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
}
static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
    GetAndProcessData(); 
    UpdateCache(); 
}

We'll see in a few paragraphs that this is exactly what you can do an ASP.NET 2.0, using the ASP.NET Cache class as your data cache.

When you use SqlDependency you are counting on the dispatcher component inside SQL Server 2005 to make a connection to the client and send the notification message. This is out-of-band communication that doesn't use SqlConnection. This also means that your client must be "network reachable" by SQL Server; firewalls and network address translation could interfere. Future betas may allow more control over port configuration to make this more firewall friendly. You can specify parameters in the SqlDependency constructor to completely configure how the server-to-client communication works. Here's an example:

SqlDependency depend = new SqlDependency(cmd,
    null,
    SqlNotificationAuthType.None,
    SqlNotificationEncryptionType.None,
    SqlNotificationTransports.Tcp,
    10000);

Using this constructor of SqlDependency allows you to choose different behaviors than the defaults. The most useful behavior to change is the wire transport the server uses to connect to the client. This example uses SqlNotificationTransports.Tcp, the server can use TCP or HTTP. The default value for this parameter is SqlNotificationTransports.Any; this lets the server "decide" which transport to use. If Any is specified, the server will choose HTTP when the client operation system contains kernel-mode HTTP support, or TCP if it doesn't. Windows Server 2003 and Windows XP with SP2 contain kernel-mode HTTP support. Because you are sending messages over the network, you can specify what authentication type will be used. EncryptionType is currently a parameter, but will be removed in later betas. Currently the default is None for both values. SqlNotificationAuthType also supports integrated authentication. You can also explicitly specify the timeout value for the subscription and the name of the SQL Server Service Broker SERVICE. The SERVICE name is usually set to null, as in the example, but you can explicitly specify the built-in service, SqlQueryNotificationService. The parameters you'd be most likely to override are SqlNotificationTransport and the timeout. Note that these parameters are only applicable to SqlDependency because they specify behaviors of the server-side dispatcher. With the SqlNotificationRequest, we don't use the dispatcher.

Using SqlNotificationRequest

Using SqlNotificationRequest is only a little bit more complex than SqlDependency on the setup side, but it's up to your program to process the messages. When you use SqlDependency, the notifications on the server will be sent to the SqlQueryNotificationService in MSDB that processes the messages for you. With SqlNotificationRequest, you must process the messages yourself. Here's a simple example using SqlNotificationRequest and the SERVICE that we defined earlier in this article.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
class Class1
{
  string connstring = null;
  SqlConnection conn = null;
  SqlDataReader rdr = null;
  static void Main(string[] args)
  {
    connstring = GetConnectionStringFromConfig();
    conn = new SqlConnection(connstring));
    Class1 c = new Class1();
    c.DoWork();  
  }
  void DoWork()
  {
    conn.Open();
    rdr = GetJobs(2);
    if (rdr != null)
    {
      rdr.Close();
      WaitForChanges();
    }
    conn.Dispose();
  }
  public SqlDataReader GetJobs(int JobId)
  {
    using (SqlCommand cmd = new SqlCommand(
        "Select job_id, job_desc from dbo. jobs where job_id = @id", 
        conn))
    {
      
    try
    {
      cmd.Parameters.AddWithValue("@id", JobId);
      SqlNotificationRequest not = new SqlNotificationRequest();
      not.Id = new Guid();
      // this must be a service named MyService in the pubs database
      // associated with a queue called notificationqueue (see below)
      // service must go by QueryNotifications contract
      not.Service = "myservice";
      not.Timeout = 0; 
      // hook up the notification request
      cmd.Notification = not;
      rdr = cmd.ExecuteReader();
      while (rdr.Read())
     Console.WriteLine(rdr[0]);
     rdr.Close();
    }
    catch (Exception ex)
    { Console.WriteLine(ex.Message); }
    return rdr;
    }
  }
  public void WaitForChanges()
  {
    // wait for notification to appear on the queue
    // then read it yourself
    using (SqlCommand cmd = new SqlCommand(
     "WAITFOR (Receive convert(xml,message_body) from notificationqueue)",    
      conn))
    {
      object o = cmd.ExecuteScalar();
      // process the notification message however you like
      Console.WriteLine(o); 
    }
  }

The power (as well as the extra work) in using SqlNotificationRequest is that you have to wait for and process the notification yourself. When you use the SqlDependency, you need not ever connect to the database again until you receive the notification. You don't really need to wait around for the SqlNotificationRequest notification; you can poll the queue every once in a while. Another use of SqlNotificationRequest might be to write a specialized application that may not even be running when the notification is fired. When the application starts up it can connect to the queue and determine which results in its "persistent cache" (from a previous application run) are now invalid.

Discussing applications that can wait around hours or days for a notification bring up the question, "If there are no changes in the data, when does the notification go away?" The only things that cause a notification to go away (i.e., be purged from the database's subscription tables) are when the notification is fired or when it expires. Database administrators who might be annoyed at having notification subscriptions hanging around (because they use SQL resources and add overhead to queries and updates) have a way to manually dispose of a notification in SQL Server. You first query one the SQL Server 2005 dynamic views and find the offending notification subscription, and then issue the command to get rid of it.

  -- look at all subscriptions
  SELECT * FROM sys.dm_qn_subscriptions
  -- pick the ID of the subscription that you want, then
  -- say its ID = 42
  KILL QUERY NOTIFICATION SUBSCRIPTION 42

Using SqlCacheDependency in ASP.NET

Notifications are also hooked up to the ASP.NET Cache class. In ASP.NET 2.0 the CacheDependency class can be subclassed, and SqlCacheDependency encapsulates the SqlDependency and behaves just like any other ASP.NET CacheDependency. SqlCacheDependency goes beyond SqlDependency in that it works whether you are using SQL Server 2005 or earlier versions of SQL Server. It's implemented completely differently for pre-SQL Server 2005 versions, of course.

When you use earlier versions of SQL Server, the SqlCacheDependency works by means of triggers on TABLEs that you wish to "watch". These triggers write rows to a different SQL Server table. This TABLE is then polled. Which TABLEs are enabled for dependencies and the value of the polling interval is configurable. The details of the pre-SQL Server 2005 implementation are beyond the scope of this article; for more information see Improved Caching in ASP.NET 2.0.

When you use SQL Server 2005, the SqlCacheDependency encapsulates a SqlDependency instance similar to the ADO.NET example shown above. Here's a short code example that illustrates using SqlCacheDependency.

// called from Page.Load
CreateSqlCacheDependency(SqlCommand cmd)
{
  SqlCacheDependency dep = new SqlCacheDepedency(cmd);
  Response.Cache.SetExpires(DateTime.Now.AddSeconds(60);
  Response.Cache.SetCacheability(HttpCacheability.Public);
  Response.Cache.SetValidUntilExpires(true);
  Response.AddCacheDependency(dep);
}

A nice ease-of-use feature is that SqlCacheDependency is even hooked into page or page fragment caching. You can declaratively enable all of the SqlCommands in a particular ASP.NET OutputCache directive. This uses the same SqlDependency for all of the SqlCommands in the page, and looks like this for a SQL Server 2005 database.

<%OutputCache SqlDependency="CommandNotification" ... %>

Note that CommandNotification is a keyword value that means "use SQL Server 2005 and SqlDependency"; the syntax for this directive parameter is completely different when earlier versions of SQL Server are used. Also, the CommandNotification keyword value is only enabled when running ASP.NET 2.0 on specific operating system versions.

Eager Notifications

A design policy of the SQL Server Query Notifications is that it is better to notify a client too often than to miss a notification. Although it's most often the case that you'll be notified when someone else has changed a row that invalidates your cache, that's not always the case. For example, if the database is recycled by the DBA, you'll get a notification. If any of the TABLEs in your query are ALTERed, DELETEd, or TRUNCATEd, you'll be notified. Because Query Notifications take up SQL Server resources, it is possible that if SQL Server is under severe enough resource stress, it will start to remove query notifications from its internal tables; you'll get a notification on the client in this case as well. And because each notification request includes a timeout value, you'll be notified when your subscription times out.

If you are using SqlDependency, the dispatcher will wrap up this information in a SqlNotificationEventArgs instance. This class contains three properties: Info, Source, and Type that will allow you to pinpoint what caused the Notification. If you are using SqlNotificationRequest, the message_body field in the queued message contains an XML document that contains the same information, but you'll have to parse it out yourself with XPath or XQuery. Here's an example of the XML document that was produced from the earlier ADO.NET SqlNotificationRequest example.

<qn:QueryNotification 
 xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" 
 id="2" type="change" source="data" info="update" 
 database_id="6" user_id="1">
<qn:Message>{CFD53DDB-A633-4490-95A8-8E837D771707}</qn:Message>
</qn:QueryNotification>

Note that although I produced this notification by changing the value of the job_desc column to "new job" in the row with job_id = 5, you'll never see this information in the message_body itself. This brings up a few final nuances of the notification process. The notification is only smart enough to know that a SQL statement altered something that could change your rowset. It is not smart enough to know that your UPDATE statement doesn't change the actual value in a row. For example, changing a row from job_desc = "new job" to job_desc = "new job" would cause a notification. Also, because Query Notifications are asynchronous and they are registered at the moment you execute the command or batch, it is possible to receive a notification before you finish reading the rowset. You can also get an immediate notification if you submit a query that does not conform to the rules; these are the rules for indexed views that I mentioned earlier.

When Not to Use Notifications: A Cautionary Tale

Because you now know how query notifications work, it's fairly straightforward to figure out where to use them: read-mostly lookup tables. Each notification rowset takes up resources in SQL Server; using them for read-only tables would be wasteful. In addition, you don't want to use them for ad-hoc queries, there would just be too many different rowsets being "watched" at the same time. A useful internal detail to know is that SQL Server folds together notification resources for parameterized queries that use different sets of parameters. Always using parameterized queries (as shown in the SqlNotificationRequest example above) will allow you to take advantage of this and provide enhanced performance. If you are worried after hearing this, bear in mind that this performance feature does not mean that you won't get the appropriate notifications. If user1 watches authors with au_lname from A-M and user2 watches au_lname from N-Z using the au_lname values as parameters, each user will only get the "right" notifications for their subset.

One last caution: when some folks think of notification applications, they envision a room full of stockbrokers with changing market prices, each screen changing continuously. This is ABSOLUTELY the wrong use of this feature for two reasons.

  1. The rowsets are changing continuously, so the network may be flooded with query notifications and query refresh requests.
  2. If there are more than a trivial number of users and they all "watch" the same data, each notification will cause MANY users to re-query for the same results at the same time. This could flood SQL Server with MANY requests for the same data!

If you think you have programmers who might abuse this feature, you'll be happy to know that post-Beta 2, SQL Server may provide more information to allow DBAs to monitor this feature through dynamic management views. Currently these views can only show you the subscriptions. Remember, it's always possible for SQL Server 2005 to "decide" that notifications are taking too many resources and start purging them itself.

Conclusion

Query notifications are a powerful new feature, built in to SQL Server 2005, usable from ADO.NET 2.0 and from ASP.NET 2.0 directly. Although the ADO.NET feature (SqlNotificationRequest and SqlDependency) ONLY works against a SQL Server 2005 database, ASP.NET makes the feature "backwards-available" through an alternate mechanism that uses polling. Use the feature wisely, with the semantics and the repercussions in mind. The "sweet spot" for this feature most likely is the read-mostly lookup table used by ASP.NET that could be updated by other applications, as well as the Web application. For this scenario, query notifications provide a solution that programmers have wanted for years.

 

About the author

Bob Beauchemin is an instructor, course author, and database curriculum course liaison for DevelopMentor. He has over twenty-five years of experience as an architect, programmer, and administrator for data-centric distributed systems. He's written articles on ADO.NET, OLE DB, and SQL Server for Microsoft Systems Journal and SQL Server Magazine and others, and is the author of A First Look at SQL Server 2005 for Developers and Essential ADO.NET.

Show: