title: Why is TestID not being converted to INT?
body: <p>Dev's and DBA's, I think I figured out a bug in SQL 2008 R2, unless one of you can explain the below. 
  Can you please explain why TestID is being converted to NUMERIC instead of INT in this scenario?
  Please be advised that this is just a sample to show you the problem I came across while trying something bigger such as an automation script that will convert data imported from Excel or Notepad that will be nvarchar/float to the appropriate data types, int, numeric, datetime, varchar. </p>
  
  <pre><code>CREATE TABLE #CorruptData(
  TestID VARCHAR(100)
   );
  
   GO
  
  INSERT INTO #CorruptData
  VALUES ('1'),
     ('2');
  
  
  
  SELECT 
       CASE
          WHEN TestID LIKE '[1-9]%' AND TestID NOT LIKE '0%' THEN CAST(TestID AS INT) 
          WHEN TestID LIKE '0%'  THEN CAST(TestID AS NUMERIC(12,2))
     END AS TestID
  
  
   INTO #FixedData
   FROM #CorruptData
  
  
  SELECT  *
  FROM    #FixedData
  </code></pre>