Bob Beauchemin does a great job in explaining what happens with Query
Notifications in this article.
Here's an excerpt:
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
SQL Server MVP
Get up to speed quickly with SQLNS
I support PASS, the Professional Association for SQL Server.
On 27 Oct 2006 08:37:57 -0700, [email protected] wrote:
>When using Sql Cache Dependency with Sql Server 2005 it's my
>understanding that you should use Query Notification. My question is
>let's say I write a Sql statement with a WHERE clause in it. Does the
>cache dependency fire if only the data within that WHERE clause changes
>or if anything in the table changes?