title: SQL Server - Partitioned Tables vs. Clustered Index?
body: <p>Let's assume you have one massive table with three columns as shown below:</p>
  
  <pre><code>[id] INT NOT NULL,
  
  [date] SMALLDATETIME NOT NULL,
  
  [sales] FLOAT NULL
  </code></pre>
  
  <p>Also assume you are limited to one physical disk and one filegroup (PRIMARY).  You expect this table to hold sales for 10,000,000+ ids, across 100's of dates (easily 1B+ records).</p>
  
  <p>As with many data warehousing scenarios, the data will typically grow sequentially by date (i.e., each time you perform a data load, you will be inserting new dates, and maybe updating some of the more recent dates of data).  For analytic purposes, the data will often be queried and aggregated for a random set of ~10,000 ids which will be specified via a join with another table.  Often, these queries don't specify date ranges, or specify very wide date ranges, which leads me to my question:  What is the best way to index / partition this table?</p>
  
  <p>I have thought about this for a while, but am stuck with conflicting solutions:</p>
  
  <p><strong>Option #1:</strong>  As data will be loaded sequentially by date, define the clustered index (and primary key) as [date], [id].  Also create a "sliding window" partitioning function / scheme on date allowing rapid movement of new data in / out of the table.  Potentially create a non-clustered index on id to help with querying.</p>
  
  <p><strong>Expected Outcome #1:</strong>  This setup will be very fast for data loading purposes, but sub-optimal when it comes to analytic reads as, in a worst case scenario (no limiting by dates, unlucky with set of id's queried), 100% of the data pages may be read.</p>
  
  <p><strong>Option #2:</strong>  As the data will be queried for only a small subset of ids at a time, define the clustered index (and primary key) as [id], [date].  Do not bother to create a partitioned table.</p>
  
  <p><strong>Expected Outcome #2:</strong>  Expected huge performance hit when it comes to loading data as we can no longer quickly limit by date.  Expected huge performance benefit when it comes to my analytic queries as it will minimize the number of data pages read.</p>
  
  <p><strong>Option #3:</strong> Clustered (and primary key) as follows: [id], [date]; "sliding window" partition function / scheme on date.</p>
  
  <p><strong>Expected Outcome #3:</strong> Not sure what to expect.  Given that the first column in the clustered index is [id] and thus (it is my understanding) the data is arranged by ID, I would expect good performance from my analytic queries.  However, the data is partitioned by date, which is contrary to the definition of the clustered index (but still aligned as date is part of the index).  I haven't found much documentation that speaks to this scenario and what, if any, performance benefits I may get from this, which brings me to my final, bonus question:</p>
  
  <p>If I am creating a table on one filegroup on one disk, with a clustered index on one column, is there any benefit (besides partition switching when loading the data) that comes from defining a partition on the same column?</p>