SqlCacheDependency / SqlDependency and columns

Question

I use the following code to cache, depending on the change in the People, Name table. However, in a row, if any other column, such as an address column, changes, the dependency also starts and clears the cache. (ASP.NET 4.0 with 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; } 

Why does it fire when the value of a column that is not in the command request has changed, although it is believed that it was run when the results changed?

You can also assign a delegate to the OnChange event, which will fire when the results are changed for the corresponding command.

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

It is also necessary to explicitly specify the columns, so we understand that it will filter out the other columns of the table and will not work.

• So why do you need to explicitly specify column names?
• Is it just to let developers know what they are doing (for example, using internal joins) and avoid creating dependencies that can lead to the worst performance?

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

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

The operator must not contain subqueries, external joins, or self-joins.

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

Answer 1

According to MS help :

SQL Server sends a subscription request notification when one of the following events occurs:

• The rows contained in the query results are subject to change.

• Subscription expires.

• The server will reboot.

• A query notification subscription cannot be created (for example, the SELECT statement does not meet the requirements specified in Creating a query for notification.

• The server is busy.

• The objects that the subscription depends on are discarded or modified.

Note that SQL Server may issue a query notification in response to events that do not change data , or in response to a change that does not actually affect the results of the query. For example, when an UPDATE statement modifies one of the rows returned by a query, the notification may be triggered even if the row update did not change the columns in the query results .

Answer 2

Since I am working with a SQL cache dependency, I noticed a lot of questions that you should consider.

One of them is you cannot cache the table depending on the column name if you want your cache tables to open AspNet_SqlCacheTablesForChangeNotification in your database.

SELECT TOP 1000 [table_name], [NotificationCreated], [ChangeId] FROM [dbo]. [AspNet_SqlCacheTablesForChangeNotification]

You will notice that when changeId is launched, click Refresh, Paste, or Delete, and this will crash the cache.

It is very important to always check your queries in a team, many queries are not supported, as you said above.

Regards and hope this helps.