title: SqlCacheDependency/SqlDependency and columns
body: <p>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.)</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>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?</p>
  
  <blockquote>
    <p>You can also assign a delegate to the OnChange event, which will fire
    when the results change for an associated command.</p>
    
    <p><a href="http://msdn.microsoft.com/en-us/library/62xk7953.aspx">http://msdn.microsoft.com/en-us/library/62xk7953.aspx</a></p>
  </blockquote>
  
  <p>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.</p>
  
  <ul>
  <li>So, why does it require to explicitly state column names? </li>
  <li>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?</li>
  </ul>
  
  <blockquote>
    <p>The <strong>projected columns in the SELECT statement must be explicitly
    stated</strong>, 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.</p>
    
    <p>The statement may not use the asterisk (*) or table_name.* syntax to
    specify columns.</p>
    
    <p>The statement must not contain subqueries, outer joins, or self-joins. </p>
    
    <p><a href="http://msdn.microsoft.com/en-us/library/ms181122(v=sql.105).aspx">http://msdn.microsoft.com/en-us/library/ms181122(v=sql.105).aspx</a></p>
  </blockquote>