SQL Server Performance

Problems in Partition table

Discussion in 'T-SQL Performance Tuning for Developers' started by kumar1980, Jul 5, 2007.

  1. kumar1980 New Member

    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 dt=@dt


    what might be the problem in that, please give solution.



  2. Adriaan New Member

    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)
  3. kumar1980 New Member

    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






    quote:Originally posted by Adriaan

    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)
  4. Adriaan New Member

    If you can run a query using this syntax ...

    declare @dt int
    select @dt =11
    select * from pv1 where dt=@dt

    ... then you can run a dynamic SQL query too. Not sure what your problem is?

Share This Page