title: SQL Server - Better Way To Join Between VARCHAR and INT
body: <p>I am looking for effective way to speed up my query. Let me describe the situation.</p>
  
  <p>Every night, we dump reports from different vendors into a table.  These are a lot of columns, but These columns below are the main columns that I use for joining in the query. </p>
  
  <p>tblVendorShippingInfo (Note that all theses column below are VARCHAR since sometimes reports contains Varchar character even in QtyShipped) </p>
  
  <pre><code>PONum     vSKU    vSKUDesc     ShipQuantity
  P111111   A       Test1       5
  P111111   A       Test1       2
  P111111   B       Test3       5
  P100002   C       Test4       6
  P222222   D       Test5       7
  P222222   D       Test5       6
  P222222   E       Test6       7
  PABC123   F       Test7       8
  XYZ123    G       Test8       8
  GHEHEH    H       Test9       8
  </code></pre>
  
  <p>tblPODetail -- Our table for Purchase Order. Note that the POID and DistyShippedQty are Integer.  The POID is only 6 digit number.</p>
  
  <pre><code>POID      SKU     SKUDesc     DistyShippedQty
  111111    A       Test1       
  111111    B       Test3       
  100002    C       Test4       
  222222    D       Test5       
  222222    E       Test6           
  </code></pre>
  
  <p>If I use the query like this below to get and update the DistyShippedQty, it would work but slow because the conversion... Matching VARCHAR...etc.</p>
  
  <pre><code>select POID, SKU
  , (
      SELECT SUM(ShipQuantity) AS ShipQuantity 
      FROM tblVendorShippingInfo
      WHERE substring(PONum, 2, 6) = Convert(varchar(10), pod.POID)
      AND vSKU = pod.SKU
      ) AS QtyCount
  FROM tblPODetail pod 
  </code></pre>
  
  <p>So, I am looking a better way to just filter the records in tblVendorShippingInfo table that only has PONum with 6 digits starting at 2nd position (after P, or any char), ignore all the records with chars (e.g.PABC123, XYZ123, GHEHEH), then index it so it can use for joining with the tblPODetail table. </p>
  
  <p>I attempted to create an indexed view with the filter, but when I run the query with this view, it failed.</p>
  
  <pre><code>Alter VIEW vw_tblVendorShippingInfo WITH SCHEMABINDING AS
  select dfID, substring(PONum,2, 6) AS POID
  , vSKU, ShipQuantity
  FROM dbo.tblVendorShippingInfo
  WHERE 1 = 1
  AND PONum like '%[^0-9]%'
  </code></pre>
  
  <p>Any recommendations would be appreciated.</p>