SqlCacheDependency/SqlDependency and columns

[QESepberege]

I am using the following code to cache with dependency on change of "People" table's, "Name" column. However, in a row if some other column such as Address column changes then also dependency fires and purges the cache. (ASP.NET 4.0 with SQL Server 2008.)\[code\]public string GetTheVals(){ string vals = HttpContext.Current.Cache["TheCacheKey__X"] as string; if (vals == null) { con = GetConnection(); SqlCommand cmd = new SqlCommand(@"SELECT NameFROM 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;}\[/code\]Why does it fire when a value of a column which is not in the query of the command changed although it is said to be fired when results changed?\[quote\] 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\[/quote\]It is also required to state columns explicitly so we understand that it will filter out other columns of the table and will not fire.

• So, why does it require to explicitly state column names?
• Is it just for making developers aware of what they are doing (such as when using inner joins) and avoid creating dependencies that will cause worst performance?

\[quote\] 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\[/quote\]