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?

Thanks...

Bob Beauchemin does a great job in explaining what happens with Query
Notifications in this article.

http://msdn2.microsoft.com/en-us/library/ms379594.aspx


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
times out.

HTH...





--
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)



[quoted text, click to view]