Intro:
Caching can improve your websites performance. This article describes how to use a specific variant of caching: using the SqlCacheDependency class to cache objects that depend on a SQL Server 2005 table.
All example code uses the Northwind example database.

Prepare the database:
In order to use the SqlCacheDependency class, the database has to be configured to use the service broker (notification services).
First check if the service broker is enabled:

         SELECT is_broker_enabled FROM sys.databases WHERE name = 'Northwind'

When this query returns 1 the broker is enabled and we are done with SQL Server.
If not, enable the broker by using this alter table statement:

         ALTER DATABASE Northwind SET ENABLE_BROKER

Two problems I have encountered using this statement:
1. It keeps running.
2. It returns error 9772: The Service Broker in database 'Northwind' cannot be enabled because there is already an enabled Service Broker with the same ID.

This problems can be solved by using resp.:
1. Change the statement to ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
2. First execute statement ALTER DATABASE Northwind SET NEW_BROKER and then again try to enable the broker.

Prepare the web application:
In the Global.asax file add the following statement to the Application_Start subroutine:
   System.Data.SqlClient.SqlDependency.Start("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")
And allmost the same statement in Application_Stop:
System.Data.SqlClient.SqlDependency.Stop("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")

Using the Cache:
The big advantage of using class SqlCacheDependency is you don't need to poll to check for changes in the database. SqlCacheDependency and SQL Server will take care of that part.
In this example we want to cache customer data of customers located in the USA. Whenever someone in someway (using another app or executing an UPDATE statement in SQL) changes something in a customer row with country equal to USA, then the cached object gets cleared, and you have to get the data again.
This example function returns a datatable of all USA customers:

    Private Function GetCustomers() As DataTable

        Dim dt As DataTable = DirectCast(HttpContext.Current.Cache.Get("cust"), DataTable)

        If dt Is Nothing Then

            Dim cn As New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")
            Dim cm As New SqlCommand("SELECT CustomerID, CompanyName, ContactName FROM dbo.Customers WHERE Country = 'USA'", cn)
            Dim dependency As SqlCacheDependency = New SqlCacheDependency(cm)
            Dim da As New SqlDataAdapter(cm)

            dt = New DataTable()
            da.Fill(dt)

            HttpContext.Current.Cache.Insert("cust", dt, dependency)

        End If

        Return dt

    End Function

Limitations:
Some limitations exist regarding the SQL statement. The most important one is to always use the table owner prefix before the table, like in the example code: dbo. Another limitation is that you are not allowed to use SELECT *.
Be aware of more limitations of your SQL.

Another problem that may occur when updating the contents of the watched table whil "SET ANSI_WARNINGS OFF" is used, is error 1934: "UPDATE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.".
So "SET ANSI_WARNINGS OFF" is not allowed when changing something in table Customers.