news:1168257760.604374.244330@11g2000cwr.googlegroups.com...
> Using the SQL 2005 "query notification" caching mechanism, I am caching
> three columns (UserId, UserName, Password) of a table called UserInfo
> in my web application cache. However, the cache gets invalidated (and
> needs to be re-loaded) when another column LastUpdateDateTime of the
> same table gets modified. This is NOT as per my expectation. Following
> is what MSDN says about Query Notifications in SQL2005
> (http://msdn2.microsoft.com/en-us/library/ms178604(VS.80).aspx).
>
> "SQL Server 2005 cache dependency is more flexible in the types of
> changes that receive notification. SQL Server 2005 monitors changes to
> the result set of a particular SQL command. If a change occurs in the
> database that would modify the results set of that command, the
> dependency causes the cached item to be invalidated. This allows SQL
> Server 2005 to provide row-level notification."
>
> This means, regardless of updates done to the table, as long as the
> result set of the query is not modified (that is, produces the same
> result), the cache should not be invalidated. This is not happening in
> my web application. I am not sure if this is an already identified
> issue with SqlCacheDependency, SQL2005 (Standard Edition) or the way I
> have used these features in my application.
>
> I have also verified this behavior with a Windows Desktop application
> and using SqlDependency object. The SqlDependency.OnChange event
> handler gets called for ANY updates performed on the table and NOT just
> for updates that modify the result set of my query.
>