Best table schema to store daily hits for many items? In my Sql Server 2005 database, I'm considering two ways of creating a table to track daily views on 100,000 items:

Option 1) Each day would insert a new row for each item. So the table columns would look like this:  

| ItemID | Date | #ofHits |  


That would mean the table grows by 100,000 rows per day.  

Option 2) Each row would contain an entire month's hits per item. So the table columns would look like this:  

| ItemID | Year | Month | Day01Hits | Day02Hits | Day03Hits | ... | Day31Hits |


That would mean the table grows by 100,000 rows per month.  

Option 3) Perhaps you have a better idea altogether?  

Keep in mind performance, such as when doing a sum of all hits between two dates (i.e. total hits for July 5th-Aug12th). Also, I'd want to keep at least 1 year of historical data.

On a related note, we're simply parsing the IIS logs nightly to get the hit data.

In summary:  

Option 1 would probably be the easiest way to query and sum data across a date range. The downside is it would add 100,000 rows every day (or a million rows everyday if we had a million items).

Option 2 would create much less rows, but probably wouldn't be efficient to query and sum data across a data range.

Is there a better way to store daily hits for many items?

Update:
Unless someone has a better design, I'm considering this:
Use option 1, but create a new table every month (this will prevent the table from growing too large). So my table names would contain the year and month, like "Stats2012_01", "Stats2012_02", etc.
Then, to query across dates (such as total hits from July 5th-Aug12th) it'd be:  

SELECT ItemID, SUM(Hits)  
FROM (  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_06 WHERE Day&gt;=5 GROUP BY ItemID  
        UNION ALL  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_07 GROUP BY ItemID  
        UNION ALL  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_08 WHERE Day&lt;=12 GROUP BY ItemID  
) s  
GROUP BY ItemID


Update 2:
I just discovered partitioning, thanks to the suggested answer from MZDBA. I'll continue to look into partitioning, which may lead to combining Option 1 with partitioning.
If performance of the site is a concern, then I would suggest going a third route.

Each time a page is viewed, insert a record into a table which is like:


  ItemId | TimeStamp


Then, each night (or whenever your site is least utilised), have a process that counts the occurences of ItemId and builds a value for a table such as you have in Option 1.

Doing it this way will be much more efficient, as an insert is ridiculously cheap compared to trying to update on a table that has 36,500,000 rows and avoids issues with locking.

I'd also strongly suggest you hold this data not on your main database, so you can query it as you like without a performace hit; and consider splitting the tables into weekly or monthly reports to cut down on the amount of data that needs to be scrolled through.