Partition View In SQL Server -- Federated Database Servers

Even after version 2000, SQL Server has meaningful ways to support real partition, it's still valuable to know the old technology because it might still fit simple situations, and a good way to federate SQL Servers. actually, federated database servers is a result of partitioned views.

Partition view will automatically move data in the underlying tables.

CREATE TABLE p1
  (id   INTEGER PRIMARY KEY
                CHECK (id BETWEEN 1 AND 100)
)
;
CREATE TABLE p2
  (id   INTEGER PRIMARY KEY
                CHECK (id BETWEEN 101 AND 200)
);
CREATE TABLE p3
  (id   INTEGER PRIMARY KEY
                CHECK (id BETWEEN 201 AND 300)
)
;
go


create view p as
select * from p1
union all
select * from p2
union all
select * from p3
;

select * from p;

insert into p(id)values(1),(105),(210);

select * from p1;
select * from p3;
select * from p2;

update p set id=211 where id=1;

When  the partition tables are on different database in different SQL Server instances, it builds up federated database servers.

For federated databases, backup and restore happen individually, but logically, it should consider to restore them to same point in time.