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/lib...4(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.
