title: WHERE clause suppresses duplicate rows
body: <p>I'm having problems with a table copy erroring due to a primary key constraint when the primary key is on the new table. In doing some investigation by not setting a primary key I was able to find a duplicate row in my source table, still unsure how it got in there but what I'm more curious about is the rest of what I found.</p>
  
  <p>Here goes:</p>
  
  <p>I have a SQL query that selects the full table and returns the following:</p>
  
  <pre><code>SELECT *
   FROM search_term_suggest
   order by search_term
  </code></pre>
  
  <p>you can see the value is duplicated and I had to scroll through the table to find it.</p>
  
  <p><img src="https://i.stack.imgur.com/i1nx7.png" alt="SQL Results"></p>
  
  <p>But when I limit my query to:</p>
  
  <pre><code>SELECT *
   FROM search_term_suggest
   where search_term = 'b'
  </code></pre>
  
  <p>you can see it filters out the first value. There are no triggers on the database or anything I can see that would limit the select query.</p>
  
  <p><img src="https://i.stack.imgur.com/IVQD7.png" alt="SQL Results with &quot;where&quot; clause added"></p>
  
  <p>I'm running SQL 2008 r2. Any help is greatly appreciated.  The database I'm copying the table to is a SQL 2000 database and when selecting using the same queries as above I get the results I would expect.</p>
  
  <p><strong>EDIT:</strong> search_term is a varchar(100), search_term_suggest is a table not a view.</p>