SQL Server: Partition view usage and limitations.

Partition views are just like any other view. They are build on two or more member tables and display records from those table with some differences and limitations. Partition can be used instead of partitioning. Enterprise edition of SQL Sever provides us facility of physical data partition of a big table. But partitioning is only available in enterprise edition. If you want to implement similar data partitioning in standard edition you can use partition views. But there are some limitations of partition views that we will see.

Partition view allows data of a larger table to be split into smaller member or base tables. We need a check constraint on column of each base table and this check constraint will determine the partition range for each member table. Range of this check constraint must be mutually exclusive in each member table so that database engine can decide on which table new incoming row should go in case of a DML.

Now take a look at one example:

create table member1 (
id int not null,
name varchar(100),
age int check (age < 18),
constraint member1_pk primary key (id, age)
);
GO

create table member2 (
id int not null,
name varchar(100),
age int check (age >= 18 AND age < 60),
constraint member2_pk primary key (id, age)
);
GO

create table member3 (
id int not null,
name varchar(100),
age int check (age >= 60),
constraint member3_pk primary key (id, age)
);
GO

create view members as
select * from member1
union all
select * from member2
union all
select * from member3;
GO

Here "members" is a partition view and "age" column of base tables will work as a partition column. As you can see check constraint on age column has exclusive conditions. And any specified value of age can satisfy, at most, one of the constraints and condition of each constraint is forming a set of disjointed or non-overlapping intervals.
One more condition for partition view is that primary key of all the member tables mush have partition column. Thats way we have to create a composite primary key having id and age columns.

Now we are going to insert some records into "members view" and see what happens to base tables:


insert into members(id, name, age)
values (1,'x', 35);

insert into members(id, name, age)
values (2,'y', 15);

insert into members(id, name, age)
values (3,'z', 5);

insert into members(id, name, age)
values (4,'p', 75);

insert into members(id, name, age)
values (5,'q', 18);

insert into members(id, name, age)
values (6,'r', 27);
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab           count
members 6
member1 2
member2 3
member3 1

So insert and select are woking file. Lets see what happens to update.

update members
set age = 79
where id = 6;
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab         count
members       6
member1       2
member2       2
member3       2

Above update statement moved row having id = 6 from member2 to member3 table.

Now talk about some limitations of partition views. INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.
For example consider this update:

update m
set m.name = 'a'
from members m inner join member1 m1 on m.id = m1.id and m1.id = 2;
GO

Msg 4439, Level 16, State 6, Line 2
Partitioned view 'eGPartitionTest.dbo.members' is not updatable because the source query contains references to partition table '[m1]'.

Also target of a MERGE statement cannot be a partitioned view.

For more details on partition view usage and limitations refer following links:
-Using partitioned views
-Create view