Nice post. I have the following situation :

My table contains 2 years of history and 5 billions of records.

I took the option to create partition per week. Each week is about 70 millions of rows and size on disk of each partition is about 4-5 GB.

After 2 years, I have 100+ physical partitions and same number of filegroups, one per partition.

I must say it runs quiet well and my table is used only to feed an olap cube.

Is this the right approach ?

My collegue told me that create so many partitions was not necessary and that SQL server would better perform if there were , let's say 4 or 5 big partitions of 200 GB each ...

Any feedback would be appreciated.