SQL Server Performance Forum – Threads Archive

Problems in Partition table

I have created partition table and view.
with using below code create table p11(dt int not null check (dt>=10 and dt<20),nam varchar(10) not null)
create table p22(dt int not null check (dt>=20 and dt<30),nam varchar(10) not null) alter table p11 add constraint pk_p11 primary key(dt)
alter table p22 add constraint pk_p22 primary key(dt) create view pv1 as
select * from p11 union all select * from p22 insert into pv1 values(11,’aaa’)
insert into pv1 values(21,’aaa’)
insertion was perfectly done in the appropriate table. when check with execution plan with below code
select * from pv1 where dt=11
scanning is happening only in one table where as using the variable it was scanning all the tables declare @dt int
select @dt =11
select * from pv1 where [email protected] what might be the problem in that, please give solution.

---

Why not use dynamic SQL: declare @dt int, @sql varchar(200)
set @dt = 11
set @sql = ‘select * from pv1 where dt=’ + CAST(@dt AS VARCHAR(100)) EXEC (@SQL)

---

Thanks for your Example we have tried with the same before, but we need in query ( not in dynamic qry). because we have done in many place like this

---

If you can run a query using this syntax … declare @dt int
select @dt =11
select * from pv1 where [email protected] … then you can run a dynamic SQL query too. Not sure what your problem is?

---

]]>