title: Using BETWEEN on a varchar field not a numeric field?
body: <p>I am using ColdFusion 8 and SQL Server 2008 R2.</p>
  
  <p>I am trying to query a column of values to get rows with a value within a range.  The column SHOULD be numeric, but it's not. It's setup as a varchar (by someone else). There are 100,000+ rows of data. Here's a FAKE sample of the data:</p>
  
  <pre><code>ID COLUMN
  1  1
  2  1.2 
  3  0.9 
  4  5 
  5  -6
  </code></pre>
  
  <p>My query looks like this:</p>
  
  <pre><code>select column
  from table
  where column between 1 and 2
  </code></pre>
  
  <p>This query won't run because the where statement's column is a varchar, and I get a conversion error, so I have to change the where statement to this:</p>
  
  <pre><code>where column between '1' and '2'
  </code></pre>
  
  <p>Now, when I run a query like this, it runs, but I don't get results. But I know that I should be seeing results, because I know that many of the values in the column field are within that range I am querying. </p>
  
  <p>I am wondering if I am seeing no results due to the field being a varchar and not a numeric. Might that be messing up my results?</p>
  
  <p>Also, we have 100,000+ records we are searching through, would there be a big performance hit by using a varchar field instead of a numeric field?</p>