As the number of table rows goes up, the performance of the database is negatively impacted. Moreover, maintainability is negatively impacted. Rebuilding a column index on 10,000 rows is fast, but on 10,000,000 rows is not.
Along comes the Partition. Splitting rows into multiple tables based on column value(s) is called Horizontal Partioning. Splitting numerous columns across tables (which is far rarer) is called Vertical Partitioning.
When you are designing a database, the use of a view helps abstract your database structure. This abstraction allows you to introduce partitions of your data without negatively impacting the consuming applications.
Take-away is, use views.
Below, I have a sample creating Horizontal Partitions. The concept of splitting records based on Year value is extremely common, but a partition can be split on any criteria you choose.
-- create three partions
create table tb_data_2004 (value varchar(50));
insert into tb_data_2004 select 'Order 123';
insert into tb_data_2004 select 'Order 234';
GO
create table tb_data_2005 (value varchar(50));
insert into tb_data_2005 select 'Order 345';
insert into tb_data_2005 select 'Order 456';
GO
create table tb_data_2006 (value varchar(50));
insert into tb_data_2006 select 'Order 567';
insert into tb_data_2006 select 'Order 678';
GO
-- bring it together
create view v_data as
select value, 2004 as [Year] from tb_data_2004
union
select value, 2005 from tb_data_2005
union
select value, 2006 from tb_data_2006
GO
-- show the data
select [Value], [Year]
from v_data order by 2 desc;
GO
-- clean up
drop table tb_data_2004;
GO
drop table tb_data_2005;
GO
drop table tb_data_2006;
GO
drop view v_data;
GO