title: single table vs multiple table for millions of records
body: <p>Here's the scenario, the old database has this kind of design</p>
  
  <pre><code>dbo.Table1998
  dbo.Table1999
  dbo.Table2000
  dbo.table2001
  ...
  dbo.table2011
  </code></pre>
  
  <p>and i merged all the data from 1998 to 2011 in this table dbo.TableAllYears</p>
  
  <p>now they're both indexed by "application number" and has the same numbers of columns (56 columns actually..)</p>
  
  <p>now when i tried </p>
  
  <pre><code>select * from Table1998
  </code></pre>
  
  <p>and </p>
  
  <pre><code>select * from TableAllYears where Year=1998 
  </code></pre>
  
  <p>the first query has 139669 rows @ 13 seconds
  while the second query has same number of rows but @ 30 seconds</p>
  
  <p>so for you guys, i'm i just missing something or is multiple tables better than single table?</p>