When you have very large data sets that reside in a single table, you can use SQL Server’s Table Partitioning to “break” the table on logical boundaries like time, and the system handles “putting the data back together” when you query it. It’s a great way to gain performance, do maintenance and more.
But there are other methods of partitioning the data than just using this feature. Before we had this function I used different tables and programming code to make inserts and reads across multiple tables. It takes more thought, but allows other benefits, such as a fine level of control on how and where you put the data. If you go this route, there are several things to keep in mind. You’ll need to consider the “ranges” of your data, and what to do when you need to query across two tables. Things like using UNION ALL (which doesn’t filter for duplicates) instead of UNION in your statements, for instance.
I’m certainly not advocating that you NOT use the Partitioning features, just that you should think everything through when you are implementing a solution. Most of the time, you have options, so learning and understanding when to use which approach is key. Picking the right strategy is why they pay you the big money.