title: MSSQL cast( [varcharColumn] to int) in SELECT gets executed before WHERE clause filters out bad values
body: <h1>Assume the following schema and query:</h1>
  <h3>Please look past the glaring design issues with having values in a varchar column that we expect to be ints.</h3>
  <pre><code>create table dbo.Parent (
      Id bigint NOT NULL,
      TypeId int NOT NULL
  )
  
  create table dbo.Child (
      Id bigint NOT NULL,
      ParentId bigint NOT NULL,
      TypeId int NOT NULL,
      varcharColumn varchar(300) NULL
  )
  
  select cast(c.varcharColumn as int)
  from dbo.Parent p (nolock)
      inner join dbo.Child c (nolock)
          on p.Id = c.ParentId
              and c.TypeId = 2
  where p.TypeId = 13
  </code></pre>
  <h1>The break:</h1>
  <p>We get a cast break due to a value that cannot be converted to an int.  In this case: &quot;123-1&quot;.  The strange thing is that the value being cast gets filtered out of the final result set.</p>
  <h2>For example, this returns zero results</h2>
  <pre><code>select c.varcharColumn
  from dbo.Parent p (nolock)
      inner join dbo.Child c (nolock)
          on p.Id = c.ParentId
              and c.TypeId = 2
  where p.TypeId = 13
      and c.varcharColumn = '123-1'
  </code></pre>
  <p>The query plan ends up looking at the Child table and actually applying the cast function before the where clause.</p>
  <p>We were able to fix this by creating a new index on the child table (it was doing a PK <em>scan</em>)</p>
  <pre><code>create index [NCIDX_dbo_Child__TypeId] on dbo.Child (
      TypeId
  )
  include (
      ParentId,
      varcharColumn
  )
  </code></pre>
  <p>It now filters on the parent table's where clause first.</p>
  <p>Is there any way to fix this without the extra index?  And again, please refrain from any suggestions related to fixing our schema.  That is definitely the proper fix in this case.</p>
  <p>I'm mostly interested in understanding why it applied the cast before it filtered the result set.</p>
  <p>Thanks</p>
  <h1>Edit - Answer:</h1>
  <p>Many thanks to both Aaron and Gordon.  If I ever get more than 15 rep points, I'll come back and up both of your replies.</p>
  <p>We ended up needing Gordon's answer since we wanted to use this query in a view.  A few folks at the office were wary of using a case statement because they prefer to have more control over ensuring that we have a smaller result set first (Aaron's answer), however it all boils down to looking at the query plan and checking your read counts.</p>
  <p>Again, thanks for all of the responses!</p>