Microsoft SQL Server 2005 - Sql cache dependency is nothing new to ASP.NET for 2005. But the way it's handled in 2005 is. In SQL Server 2005, the polling model is no longer used as it was used in SQL Server 2000. Rather, in 2005 ASP.NET uses SQL Notification services to notify subscribing applications that a change has been made on the table. Prior to SQL Server 2005, the ASP.NET application spawns a thread that polls the specified database tables looking for a change to the underlying data. When a change occurs then and only then is a call to the database made and the data is pulled back to fill your datasource.

So what does SQL cache dependency buy us? It avoids the overhead of making round trip calls to the database thus eleviating traffic on chatty applications. It allows your record set to be refreshed with current data. So how do you enable SQL Cache dependency? A good start on SQL Server 2005 is by runntng the utility tool aspnet_regiis.exe. You can also run this via the command line, but the gui is intuitive. By running the interface you tell SQL which database you want to enable cache dependency on, it will also enable the chosen database for SQL Role Membership using the SQL Role Provider which can be discussed in a different thread.