SQL Server high-speed cache dependency analysis

1. Enable the database to support SQL high-speed cache dependencies.
2. Enable the table to support SQL high-speed cache dependencies.
3. The web. config file of the ASP. NET application contains the SQL connection string.
4. Use SQL high-speed cache dependency as follows:
1) create a SqlCacheDependency object by programming in the code.
2) Add the SqlCacheDependency attribute to the OutputCache command.
3) Use Response. AddCacheDependency to add a SqlCacheDependency instance to the Response object.

Brief description of parameters for enabling high-speed cache dependency
-D <database>The name of the database used for SQL high-speed cache dependency. The database can be specified using the connection string and the-c option.(Required)
-EdAllows the database to enable SQL high-speed cache dependency.
-DdDisable the database from enabling SQL high-speed cache dependencies.
-EtAllows the table to enable SQL high-speed cache dependency. The-t option is required.
-DtDisable the SQL high-speed cache dependency on tables. The-t option is required.
-T <table>Supports or disables SQL high-speed cache-dependent table names. -Et or-dt is required.
-LtLists All Tables with SQL high-speed cache dependency enabled.

In SQL Server2000Use the high-speed cache disabling function (the following settings can also be performed in 2005)
To disable the cache function in SQL 2000, perform the following two steps:
The first step is to make the corresponding database support this function.
Step 2: Enable the table to be used to support this function.
Example:
Aspnet_regsql-S localhost-U sa-P sa-d Northwind-ed
Note:Enable the cache dependency function for the Northwind database on the local machine.
Aspnet_regsql-S localhost-U sa-P sa-d Northwind-dd
Note:Disable the high-speed cache dependency function of the Northwind database on the local machine. After the command is run, the cache disabling function for all tables in the database is automatically disabled, and the AspNet_SqlCacheTablesForChangeNotification table is also automatically deleted.
Enable high-speed cache dependency for tables.
Example:
Aspnet_regsql-S localhost-U sa-P sa-d Northwind-t Products-et
Note:Enable the high-speed cache dependency function for the Products table in the Northwind database.
Aspnet_regsql-S localhost-U sa-P sa-d Northwind-t Products-dt
Note:Disable the cache dependency function for the Products table in the Northwind database. This method enables the cache dependency function for one or more tables in a database. After the cache dependency function is enabled for the database and table, you can find that an AspNet_SqlCacheTablesForChangeNotification table is added to the Northwind database, indicating that the configuration is successful.
Aspnet_regsql-S localhost-U sa-P sa-d Northwind-lt
Use the-lt parameter to check which tables in the database have enabled the cache dependency function. If the High-speed cache dependency is disabled or the high-speed cache dependency function is not enabled for the database, an error message is displayed when you use this command.

About the high-speed cache disabling function of SQL 2005
SQL Server 2005 supports high-speed cache programming and declarative technology. You can use the CommandNotification string in the OupputCache command to create a notification-based high-speed cache for the page. You can specify the SQL high-speed cache option or declare the option programmatically, but you cannot use both methods. The cache dependency of SQL2005 does not require web. you do not need to enable the cache dependency function for the database and table in the config configuration. Because 05 has a built-in notification delivery service, this service can detect data changes and implement change notification transmission, therefore, you do not need to constantly poll the database to detect data updates.
Note: although the cache dependency in 05 is very easy to use, I have not found more relevant information and have not personally tested it. The main reason is that I have not figured out how to enable the Broker service, you have time to perform the test later.

Configure ASP. NET Applications
After the database and several tables support the SQL Server cache disabling function, you must configure applications for the SQL Server cache disabling function.
To configure the application to use the SQL Server cache disabling function, you must first modify the web. config file. In this file, specify the database to be used and connect ASP. NET to it.
For example:
<ConnectionStrings>
<AddName = "NorthwindConnectionString"ConnectionString = "Data Source =.; Initial Catalog = Northwind; User ID = sa; Password = sa"
ProviderName = "System. Data. SqlClient"/>
</ConnectionStrings>
<System. web>
<Caching>
<SqlCacheDependency enabled = "true" pollTime = "500">
<Databases>
<Clear/>
<Add name = "Northwind" connectionStringName = "NorthwindConnectionString" pollTime = "500"/>
</Databases>
</SqlCacheDependency>
</Caching>
</System. web>
Note: The <connectionStrings> element in the web. config file is used to establish a connection string with the database. Then, use the <caching> element to configure the SQL Server cache function. If you use SQL Server high-speed cache dependencies, you must use the <sqlCacheDependency enabled = "true"> node and enable this function. Reference the database in the <databases> node.
<Add> attribute description in the element:
Name: Provides an identifier for the SQL Server database.
ConnectionStringName: Specifies the connection name.
PollTime: Specifies the time interval for SQL Server to take turns detection. The default value is 5 seconds or 500 milliseconds. This attribute is not required for the SQL 2005 notification function.
After the web. config file is correctly configured, you can disable the SQL Server cache function on the page. ASP. NET will put the SQL Server request on a completely different thread in the AspNet_SqlCacheTablesForChangeNotification table to see if the changeId number is increasing. If the number changes, ASP. NET will know that the underlying SQL Server table has been modified, and a new result set should be retrieved. It determines whether an SQL Server call should be performed. The request to the AspNet_SqlCacheTablesForChangeNotification table will get a result. After the SQL Server high-speed cache disabling function is enabled, the execution will be very fast.

Add multiple tables to the page as follows (separated by semicolons ):
<% @ OutputCache Duration = "3600" VaryByParam = "none"SqlDependency = "Northwind: Employees; Northwind: MERs"%>