Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts.
On average, there are 15 million transactions per day. More than 5 billion per year, over 60G per decade. That's not big data, but it's a lot.
How many rows can a SQL Server 2012 table hold?
Of course, older data is used less often and is candidate for being partitioned in multiple tables on the same DB. But when should this partitioning start happening? 1 table per year? 5 years?
Additional information collected from comments:
Consider: I have enough storage to hold 30 billion records of that event. If each event record requires 1KB, I have 30TB in that table, and enough storage for that (and for log of it). Its PK is bigint.
What do you think of having one table with historical data and another table with latest data? Instead of a transactional event, the table has a catalog, clients, for example. Every day the OLTP's catalog is copied into DW. So I'd have a table holding the history data and another table with the latest records.
In the design I use, ETL feeds the history table, then I use row_number() to grab the latest record of each entity by its NK. It's very expensive to run, but this way I keep entities that existed in the past and aren't on the OLTP anymore.