As you may have heard SQL Server 2005 supports automatic cache invalidation with output caching and datasource controls by using notifications. It seems simple enough to set up, but there are a number of things that you can put in a query that will keep the query from caching. Here is a brief list and a link to the complete list.
- Queries with * in them
- Queries with aggregates such as SUM, AVG, or user defined.
- Queries with unnamed columns
- Queries with computed columns
- Queries that reference tables on other servers.
- Queries with outer joins or subqueries
The list goes on and on. Be sure and read it before you try to use a SqlCacheDependency. Cache depencies are really cool, but you have to know works and what doesn't or it will most certainly lead to frustration.
Creating a Query for Notification
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=324