Jerry Nixon @Work: Partition SQL Tables

Jerry Nixon on Windows

Thursday, May 18, 2006

Partition SQL Tables

There is no real limit to the number of rows in a single SQL table. I had a MCS guy tell me they had a running competition and around 70 billion rows was the current record. He followed-up with saying you could query the table in an impressive 3 hours.

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