What Are the Limitations of SQLdependency

What are the limitations of SqlDependency?

The most complete list I can find (from here) is as follows:

• 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.

Additional reference:

• Working with Query Notifications

Why can't TOP be used with SQLDependency?

This article explains how Query Notifications technology leverages the indexed views technology and therefore it has the same restrictions. In order to create an efficient indexed view one has to be able to update the index only from the current update, w/o looking at any other row in the table. If the TOP would be allowed then the million dollar question is: if you delete a row that was in the TOP which row should take its place? Answering this would require to search in the table another row that should be now included in the TOP indexed view, instead of the deleted one. Therefore and indexed views containing TOP (or, for the matter, MAX or MIN which suffer from the very same problem) cannot be maintained efficiently and are not permitted.

You can detect that a row was inserted by querying COUNT_BIG(*). Once you're notified that the count has changed, querying for the newly inserted row is trivial. You will also be notified on DELETES (ie. false positives).

does SqlDependency lock table?

To answer your question, SqlDependency will not 'lock' the table, but may increase lock contention in high-write environments as it uses the same mechanism as indexed views to detect changes to underlying data.

However, it should be a good fit unless:

• The frequency of changes is likely to be high. To define 'high', you really need to test your ecosystem, but a suggested guideline is that if your data changes many times per second, it's probably not a good fit as you: the response time is not guaranteed for SqlDependency, and the callback mechanism is not designed to reliably handle many concurrent changes where you need to be notified of every change. In addition, the SqlDependency can increase blocking/contention on the underlying table as the index used to keep track of changes can form a bottle-neck with a high frequency of writes.

• You are intending to build the SqlDependency into a client application (e.g. desktop app) which accesses the database directly, and of which there will be many instances. In this case, the sheer volume of listeners, queues and messages could impact database performance and is just inefficient. In this case you need to put some middleware in between your database and your app before thinking about SqlDependency.

• You need to be reliably notified of every single change. The mechanism underlying SqlDependency within SQL Server will generate a notification for every change, but the .NET side of things is not inherently designed to handle them in a multi-threaded way: if a notification arrives while the SqlDependency's worker thread is already handling another notification, it will be missed. In this case, you may be able to use SqlNotificationRequest instead.

• You need to be notified immediately of the change (i.e. guaranteed sub-second). SqlDependency is not designed to be low-latency; it's designed for a cache-invalidation scenario.

If SqlDependency is not a good fit, have a look at the Planning for Notifications and underlying Query Notifications pages on MSDN for more guidance and suggestions of alternatives. Otherwise see below for a bit more detail on how to assess performance based on the underlying technologies at play.

SqlDependency largely relies upon two key SQL Server technologies: query notifications (based on indexed views), and service broker. It effectively hooks into the mechanism that updates an indexed view whenever the underlying data changes. It adds a message to a queue for each change, and service broker handles the messaging and notifications. In cases where the write frequency is very high, SQL Server will work hard to handle the writes, keep its 'indexed view' up-to-date, as well as queueing and serving up the many resulting messages. If you need near-instant notification, this may still be the best approach, otherwise have a look at either polling, or using an After Update trigger which perhaps uses Service Broker as suggested on MSDN.

SQL dependency and data refering in SQL

The SqlDependency class has a lot of restrictions as well as the memory leak problems. An absence of the TOP instruction is the one of them. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Filtering incoming messages helps you implement desirable behavior. Hope this help.

Why does my SqlDependancy OnChange() event not fire?

Queries with a SqlDependency have a number of requirements. When a query is executed that doesn't meet these requirements, the ChangeEventHandler fires immediately with Invalid in SqlNotificationEventArs.Info.

In this case, the query is invalid because the table name was not schema-qualified. Specify a 2-part name so that the query is valid for notifications. This example assumes the dbo schema:

string query = "SELECT AREA_ID, VEHICLE_COUNT, ADDED_ON FROM dbo.CAPACITY_LOG";

This change should result in a valid SqlDependency. Note that when the ChangeEventHander is invoked, the normal pattern is to execute the query again with SqlDependency, which will get the latest data and re-subscribe to change notifications.

---

Related Topics