SqlCacheDependency - Cache Invalidation wihout result set ..

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.

Why the X-Post?

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc

<mrashidsaleem.DeleteThis@hotmail.com> wrote in message
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.
>