title: SQL Server 2008 Partitioned Table and Parallelism
body: <p>My company is moving to SQL Server 2008 R2.  We have a table with tons of archive data.  Majority of the queries that uses this table employs DateTime value in the where statement.  For example:</p>
  
  <p>Query 1</p>
  
  <pre><code>SELECT COUNT(*) 
  FROM TableA 
  WHERE 
       CreatedDate &gt; '1/5/2010' 
       and CreatedDate &lt; '6/20/2010'  
  </code></pre>
  
  <p>I'm making the assumption that partitions are created on CreatedDate and each partition is spread out across multiple drives, we have 8 CPUs, and there are 500 million records in the database that are evenly spread out across the dates from 1/1/2008 to 2/24/2011 (38 partitions).    This data could also be portioned in to quarters of a year or other time durations, but lets keep the assumptions to months.</p>
  
  <p>In this case I would believe that the 8 CPU's would be utilized, and only the 6 partitions would be queried for dates between 1/5/2010 and 6/20/2010.  </p>
  
  <p>Now what if I ran the following query and my assumptions are the same as above.</p>
  
  <p>Query 2</p>
  
  <pre><code>SELECT COUNT(*) 
  FROM TableA 
  WHERE State = 'Colorado'
  </code></pre>
  
  <p>Questions?<br>
      1. Will all partitions be queried?   Yes<br>
      2. Will all 8 CPUs be used to execute the query?   Yes<br>
      3. Will performance be better than querying a table that is not partitoned?  Yes<br>
      4. Is there anything else I'm missing?<br>
      5. How would Partition Index help?    </p>
  
  <p>I answer the first 3 questions above, base on my limited knowledge of SQL Server 2008 Partitioned Table &amp; Parallelism.  But if my answers are incorrect, can you provide feedback any why I'm incorrect.</p>
  
  <p>Resource:    </p>
  
  <ul>
  <li>Video: <a href="http://channel9.msdn.com/Blogs/Joe+Longtin/Demo-SQL-Server-2008-Partitioned-Table-Parallelism" rel="nofollow">Demo SQL Server 2008 Partitioned Table Parallelism</a> (5 minutes long) </li>
  <li>MSDN: <a href="http://msdn.microsoft.com/en-us/library/ms188706.aspx" rel="nofollow">Partitioned Tables and Indexes</a> </li>
  <li>MSDN: <a href="http://msdn.microsoft.com/en-us/library/ms191174.aspx" rel="nofollow">Designing Partitions to Manage Subsets of Data</a> </li>
  <li>MSDN: <a href="http://msdn.microsoft.com/en-us/library/ms345599.aspx" rel="nofollow">Query Processing Enhancements on Partitioned Tables and Indexes</a></li>
  <li>MSDN: Word Doc: <a href="http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx" rel="nofollow">Partitioned Table and Index Strategies Using SQL Server 2008</a> white paper</li>
  </ul>
  
  <p>BarDev</p>