SQL cache failure new functions in version 2.0

Document directory

• SQL Server 2005 notification-based cache failure

Cache technology is a new technology in. net2.0. Following the previous understanding of the cache, the first cache-based round robin failure was used last time. The following is a supplement in all aspects.

Polling-Based Failure

This mechanism uses the round robin checklist to check whether the page has been updated since it was cached. To enable table-based caching, follow these steps:

1)	Use the aspnet_regsql.exe tool to enable notifications for databases. >aspnet_regsql.exe -S "."SQLExpress" -E -d "pubs" -ed This step only needs to be performed once for each database.
2)	Use the aspnet_regsql.exe tool to enable notifications for the tables to be dependent on. >aspnet_regsql.exe -S "."SQLExpress" -E -d "pubs" -et -t "authors"
3)	Register the notification in the application configuration. <system.web> <caching> <sqlCacheDependency enabled="true" pollTime="1000" > <databases> <add name="PubsDB" connectionStringName="Pubs" /> </databases> </sqlCacheDependency> </caching></system.web> The polling time specifies the interval at which the application checks whether the data has been changed.
4)	Then you can use an SQL dependency on the OutputCache command: <%@ OutputCache Duration="999999" SqlDependency="Pubs:Authors" VaryByParam="none" %> Or the dependencies can be specified directly on the data source control: <asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite" SqlCacheDependency="PubsDB:Authors" ... />

The following example uses the output cache of the SQL data source (using table-based notifications. Monitors the timestamp at the bottom of the application. The timestamp should be kept static. You can use the GridView example to edit the data. The updated data and new timestamp should be displayed on the refresh page.

SQL Server 2005 notification-based cache failure

This mechanism uses the SQL Server 2005 query change notification mechanism to detect changes to query results. Unlike the polling-based invalidation of SQL Server 7.0 and 2000, notification-based invalidation requires much less settings.

1. Unlike polling-based invalidation, you do not need to register any<sqlCacheDependency>. Additionally, you do not need to use the aspnet_regsql.exe tool for any special configuration.

2. Notification-based dependencies use stringsCommandNotificationConfigured on the OutputCache command. This value indicates that ASP. NET should create notification-based dependencies for pages or data source controls.

   On the page:

   <%@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="none" %>

   On the data source control:

   <asp:SqlDataSource EnableCaching="true" SqlCacheDependency="CommandNotification" CacheDuration="Infinite" ... />

3. Before executing an SQL query for the first time, you must call the System. Data. SqlClient. SqlDependency. Start () method somewhere in the application. This method should be placed in the Application_Start () event of the global. asax file.

Each time a command is sent to SQL Server 2005, ASP. NET and ADO. NET automatically create a cache dependency that listens for change notifications sent from SQL Server. When the data is changed in SQL Server, these notifications will invalidate the cached query on the Web Server. When a page or data source control associated with the dependency is requested, the page or data source control is executed again, instead of providing cached information.

To prevent queries from using SQL Server 2005 to query notifications, see:

1. The SELECT statement must explicitly contain the column name. Using "SELECT *" will prevent the query from being registered in the SQL Server 2005 query notification.
2. The table name in the query must include the owner name. For example, if you send a query to the authors table in the pubs database, the query must use "dbo. authors" to reference the table.
3. The Security ID for running the query must have the permission to register the query notification in SQL Server 2005. This permission can be granted using the following T-SQL command:GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
4. The Security ID for running the query must also have the permission to send query notifications from SQL Server 2005. This permission can be granted using the following T-SQL command:GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.

Note:There are many restrictions on the query syntax that supports query notifications. For a list of constraints, see the topic "create for notification query" in SQL Server 2005 Books Online (SQL Server 2005 Books Online ". In addition, if the query is not cached but executed on each page request, it may be that the query does not comply with the constraints required by SQL Server 2005, or SQL Server 2005 produces an error when trying to set the notification for this query. Currently. when cache dependencies are set in. NET, any of these conditions will result in errors without any prompts. The final result is that the cache dependencies are always invalid, therefore, all associated queries are always executed on each page request.