Here is a digest of the series of posts I made on SQL Server 2005 Cache Invalidation:
This post covers the two most simple options for using cache invalidation with ASP.NET.
Shows how to create a dependency on a database table for your System.Web.Caching.Cache item.
Reviews an implementation that allows you to wire an event handler to handle the change notifications on your own.
Covers the lower level framework implementation of how SQL notifications work. Provides essentially the same functionality as SqlDependency, but is a good intro to SQL Server Service Broker programming.
This post covers how to use SQL Notifications to invalidate cache on a granular (row by row or even column by column) level. If you've dismissed database cache invalidation in the past because your cache strategy is too complicated, you should check this out and see if it works for you.