create table test_1
(
	a int primary key check ( a between 1 and 10)
)
GO

create table test_2
(
	a int primary key check ( a between 11 and 20)
)
GO

insert into test_1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10

insert into test_2
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20
GO

create view vw_test
as
select * from test_1
union all
select * from test_2
Go

select * from vw_test where a between 1 and 5

quote:

---

Originally posted by harsh_athalye

Hi all,

To improve the performance, I splitted the single heavy table in my db into two new tables and created a partitioned view on it. Following is an oversimplified example:

create table test_1
(
	a int primary key check ( a between 1 and 10)
)
GO

create table test_2
(
	a int primary key check ( a between 11 and 20)
)
GO

insert into test_1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10

insert into test_2
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20
GO

create view vw_test
as
select * from test_1
union all
select * from test_2
Go

select * from vw_test where a between 1 and 5

When I execute the above query and see the execution plan, I see that both the tables are seen as a part of execution plan even though my WHERE clause is satisfied only by the first table, what is the reason behind this?

This does not happens when I use = or IN operators !

Can anybody please explain this behavior?

Thanks.

Harsh Athalye
India.
"Nothing is Impossible"

---