asp.net - SqlCacheDependency/SqlDependency 和列

Question

我正在使用以下代码来缓存，并依赖于“People”表的“Name”列的更改。但是，在一行中，如果其他列(例如“地址”列)发生更改，则依赖项也会触发并清除缓存。 (ASP.NET 4.0 与 SQL Server 2008。)

public string GetTheVals()
{
    string vals = HttpContext.Current.Cache["TheCacheKey__X"] as string;
    if (vals == null)
    {
        con = GetConnection();
        SqlCommand cmd = new SqlCommand(@"
SELECT Name
FROM dbo.People
", con);            
        con.Open();
        SqlCacheDependency sqlDependency = new SqlCacheDependency(cmd);
        SqlDataReader rdr = null;

        StringBuilder builder = new StringBuilder("");
        rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            builder.Append(rdr[0].ToString());
        }
        vals = builder.ToString();

        HttpContext.Current.Cache.Insert("TheCacheKey__X", vals, sqlDependency, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(20));
        CloseConnection(con);
    }
    return vals;
}

尽管据说在结果更改时会触发，但为什么当不在命令查询中的列的值更改时会触发？

You can also assign a delegate to the OnChange event, which will fire when the results change for an associated command.

http://msdn.microsoft.com/en-us/library/62xk7953.aspx

还需要明确声明列，以便我们了解它将过滤掉表中的其他列并且不会触发。

• 那么，为什么需要显式声明列名称？
• 这只是为了让开发人员了解他们在做什么(例如使用内部联接时)并避免创建会导致性能最差的依赖项吗？

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

The statement may not use the asterisk (*) or table_name.* syntax to specify columns.

The statement must not contain subqueries, outer joins, or self-joins.

http://msdn.microsoft.com/en-us/library/ms181122(v=sql.105).aspx

Answer 1

根据MS help ，但是:

SQL Server sends a query notification for a subscription when one ofthe following events occurs:

• Rows contained in the query results may have changed.

• The subscription expires.

• The server restarts.

• The query notification subscription could not be created (for example,the SELECT statement does not conform to the requirements specified inCreating a Query for Notification.

• The server is heavily loaded.

• Objects that the subscription depends on are dropped or modified.

Notice that SQL Server may produce a query notification in response toevents that do not change the data, or in response to a change thatdoes not actually affect the results of the query. For example, whenan UPDATE statement changes one of the rows returned by the query, thenotification may fire even if the update to the row did not change thecolumns in the query results.