ASP.NET SQL cache dependency offers the following features:

• You can use SQL cache dependency for both the application cache and the page output cache.

• You can use SQL cache dependency with SQL Server 7.0 and later versions.

• You can use SQL cache dependency in a Web garden (multiple processors on one server) or a Web farm (multiple servers running the same application).

• The database operations associated with SQL cache dependency are simple and therefore do not incur a heavy processing cost on the server.

• You do not need extensive SQL knowledge to configure SQL cache dependency in your application and in SQL Server. ASP.NET includes tools that automate the configuration. Additionally, you can use the SqlCacheDependencyAdmin class to programmatically configure SQL cache dependency.

ASP.NET implements a poll model for SQL Server 7.0 and SQL Server 2000 cache dependency. A thread within the ASP.NET process polls the SQL Server database at a specified time interval to determine whether the data has changed. If so, dependent cache items are invalidated and removed from the cache. You can specify the poll interval in your application declaratively in the Web.config file or programmatically using the SqlCacheDependency class.

SQL cache dependency is limited to data changes at the table level for SQL Server 7.0 and SQL Server 2000. You can configure ASP.NET to poll the database for changes in a table, but not in a specific row.

Enabling SQL Caching

SQL Server 2005 implements a different model for cache dependency than SQL Server 7.0 and SQL Server 2000. You do not need to go through any special configuration steps to enable SQL cache dependency on SQL Server 2005. Additionally, SQL Server 2005 implements a change notification model where notifications are sent to subscribing application servers, rather than relying on the polling model required in earlier versions of SQL Server.

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.

There are some requirements for the query used to test for changes. You must provide fully qualified table names, including the owner name (for example, dbo.authors). In general, SQL 2005 notification supports Select queries as well as stored procedures, and supports multiple and nested queries, but does not support aggregate operations such as COUNT(*). For more information about what queries are supported and the rules for notification in SQL Server 2005, see the SQL Books Online topic named "Creating a Query for Notification."

Once you have configured SQL Server 7.0 or SQL Server 2000 for cache dependencies, or have created the appropriate command dependency in SQL Server 2005, you can configure your application to use SQL cache dependency just as you would configure any other cache dependency. For example, you can create a cache profile in the Web.config file and then reference that cache profile on each page that should use the SQL cache dependency. You could also use SQL cache dependency by enabling it programmatically using the SqlCacheDependency class. For more information, see How to: Cache Page Output with Cache Key Dependencies.

Tasks

Concepts