What are the limitations of SqlDependency?

Question

I am using a table as a message queue and "signing up" for updates by using a SqlDependency. Everywhere I read, people are saying "look out for the limitations of it" but not specifically saying what they are. From what I've gathered, you will have problems when the table has very high update frequency; fortunately, I'm only looking at 10 - 20 values per minute maximum.

What are the other limitations/impact on the SqlServer?

Gonococcus

I just implemented something similar using SQL Service Broker and NServiceBus. I queue up database events in service broker and then pull the messages using the my service application to publish with NServiceBus. It works wonderfully. – Spike

Answer 1

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

Dichroscope

Most of these rules/limitations are obvious with a few important ones. I don't really see what everyone is always going on about. – Gonococcus

@Smudge202, What about Stored Procedure? Can SqlDependency work with Stored Procedure? – Sempach

@Sempach Yes, though the stored procedures are subject to the same limitations. In addition, I do not believe you are allowed to "SET NOCOUNT ON" in the SP. – Dichroscope

@Smudge202,So is there any way to get rid of these long list of limitations?Or do you know any other way instead of sqldependency to give me? – Sempach

@Sempach Depends on what you're trying to do. I recommend watching what happens in SQL Profiler when you set up a SqlDependency to help you reverse engineer it. You can replicate some of the behaviour without so many limitations by using triggers on your tables, but that would not help if you need to monitor dynamic sets of data (it would only work for each table you place triggers on). I would recommend using the Sql Dialog/Conversation logic that Sql Dependency uses in order to accurately/reliably notify your application of changes. – Dichroscope

Seems you can set the SET NOCOUNT ON in SP :) http://technet.microsoft.com/en-us/library/aa259204(v=sql.80).aspx – Kyte

hi i am now working with sql dependency. can i use top clause in sql statement? please guide. thanks – Ramulose

@Mou, That's just rude - take the time to read the answer: ""The statement must not use the TOP expression."". – Dichroscope

How is the first link "Understanding SQL Dependencies" related to query notifications and the SqlDependency mechainism in ADO.NET? – Bakeman

@Bakeman if you reverse engineer the SqlDependency class (at least when this answer was first created, though I've no idea if the mechanism has changed in the last 7 years...) you'll find that the class creates Query Notifications under the hood. You can see this yourself if you start SQL Profiler and then create a SqlDependency. – Dichroscope

Yes, but SqlDependency is from what I understood a completely different thing than the TSQL SQL dependencies the first article descibes. The article talks about WITH SCHEMABINDING, and this is completely unrelated, correct me if I'm wrong. – Bakeman

It's been a very long time since I had anything at all to do with Query Notifications and the SqlDependency class, so I can't claim to be an authority on it.I agree that the first additional article does point to something completely unrelated though - I'll remove it. No idea if it used to point to something of use and the content moved or if it's always been wrong. – Dichroscope

These requirements include those for SCHEMABINDING. Which makes sense, since "The query notifications functionality builds on the change detection mechanisms that the Database Engine uses to maintain indexed views", and indexed views require SCHEMABINDING. – Gingerly

Answer 2

In addition to this, for anyone else thinking about using SqlDependency to receive notifications about changes, I've been using this approach in production, and I'm finding problems with it. I'm looking into it to see if the problems are related to my code, but the main issues are:

• If you fire multiple changes in quick succession, you don't always get the equivalent number of events coming through to the code. In my code, if 2 new records are inserted one after the other, I only get the one notification (for the last one).

• There is no way to know the record that was added. So if you add a new record, and the code fires to receive the notification, there is no way in the code to know the id of that new record, so you need to query the database for it.

Huntress

I made the same observations. At the time I went as far as reverse engineering SqlDependency with a combination of reflector and SQL Profiler. The first issue is because the notification you receive collapses the callback you have set up, which in turn sets up a new callback. There is a gap between the first callback firing and the second being setup. If you check how the SQL Dialogs/Conversations/Queuing are setup using SQL Profiler, you can create a custom implementation that returns details about the record inserted if you want, which would deal with the second issue. Good luck – Dichroscope

About the multiple changes: Every SqlDependendy fires onChange only once. Thus if you change the data again before having set up the SqlDependency again you will get less onChange-class than database-changes. IMO the idea behind SqlDependency is to use it to invalidate cached data an read the whole set again after a change. – Markhor

for the second point, if you want to capture the newly added record, you can use SqlTabledependency instead of SqlDependency . More information here: recordtablechanges.codeplex.com – Unseat

Answer 3

Spent a day chasing down an issue with SQL Service Broker not working, the root cause was referencing the database in the stored procedure.

For example, this select works fine in SQL Management Studio:

select [MyColumn] from [MyDatabase].[MySchema].[MyTable]

However, this is rejected by SQL Service Broker because we are referencing the database in the select statement, and the callback from SqlDependency comes back with Invalid in SqlNotificationEventArgs e, see http://msdn.microsoft.com/en-us/library/ms189308.aspx.

Altering the SQL passed into SqlDependency to the following statement eliminated the error:

select [MyColumn] from [MySchema].[MyTable]

Update

The example above is just one of many, many limitations to the SQL statement that SQL Service Broker depends on. For a complete list of limitations, see What are the limitations of SqlDependency.

The reason? The SQL statement that SQL Service Broker uses is converted, behind the scenes, into instructions to monitor the SQL Transaction Log for changes to the database. This monitoring is performed in the core of SQL Server, which makes it extremely fast when it comes to detecting changes to table(s). However, this speed comes at a cost: you can't use just any SQL statement, you must use one that can be converted into instructions to monitor the SQL Transaction Log.

Titbit

Answer 4

Note that you cannot use a nolock hint in the stored procedure or the dependency will remain constantly invalid and therefore any cache you make on it will permanently re-query the database.

with (NOLOCK) 

This does not appear to be mentioned in the documentation (as far as I can tell).

The following SET options are required prior to the procedure script

SET ANSI_NULLS ON
SET ANSI_PADDING ON  
SET ANSI_WARNINGS ON

Others have argued that these SET options are also required, but I don't think they are. It's a good idea to set them like this anyway though.

SET CONCAT_NULL_YIELDS_NULL ON 
SET QUOTED_IDENTIFIER ON 
SET NUMERIC_ROUNDABORT OFF 
SET ARITHABORT ON

Auxochrome

Answer 5

Another big issue I have with this technology: the need for the subscriber connection to have Create Procedure permissions. The web service layer of my application at work at the moment runs as a restricted user. To get notifications setup using SQLDependency I'd have to open up that user to create procs. Sounds like a pretty good step along the path of getting owned.

Gendarmerie

The login that creates the stored procedures should be tied to its own schema and only have authorisation by that schema so that by default it does not have access to any data / procedures that it has not created itself. Doing this means that if a hacker was able to create their own stored procedure then they would not be able to do anything nefarious with it – Auxochrome

Answer 6

To overcome these limitations, you can try use the SqlTableDependency. Have a look at www.sqltabledependency.it

Mackenziemackerel

Site moved to github.com/christiandelbianco/… – Simplistic

Answer 7

It uses Service Broker. Therefore it won't work on non managed SQL Azure instances. So be cautious if you're using SQL Azure or ever might.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-features

Service Broker

Supported by single databases and elastic pools:

No

Supported by managed instances:

Yes, but only within the instance. See Service Broker differences

So probably not a good fit unless all your environments can use it!

Carmelcarmela

Answer 8

SqlDependency does not work with memory-optimized tables. I did not find it in the documentation but in my app it does not.

Mousy