SQL Server Performance

Clustered Index Scan vs Clustered Index Seek

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by wkm1925, Jan 27, 2010.

  1. wkm1925 New Member

    --My table as follow,
    CREATE TABLE DERPosi_201001
    (
    [TID] int not null,
    [SeatN] [numeric](15, 0) NOT NULL,
    [Posi] [varchar](30) NOT NULL,
    [recGrp] [datetime] not null,
    Constraint ck_recGrp_201001 CHECK (
    recGrp BETWEEN '20100101' and '20100131'
    )
    )
    alter table DERPosi_201001 add primary key clustered(TID,recGrp)
    CREATE TABLE DERPosi_201002
    (
    [TID] int not null,
    [SeatN] [numeric](15, 0) NOT NULL,
    [Posi] [varchar](30) NOT NULL,
    [recGrp] [datetime] not null,
    Constraint ck_recGrp_201002 CHECK (
    recGrp BETWEEN '20100201' and '20100228'
    )
    )
    alter table DERPosi_201002 add primary key clustered(TID,recGrp)
    CREATE TABLE DERPosi_201003
    (
    [TID] int not null,
    [SeatN] [numeric](15, 0) NOT NULL,
    [Posi] [varchar](30) NOT NULL,
    [recGrp] [datetime] not null,
    Constraint ck_recGrp_201003 CHECK (
    recGrp BETWEEN '20100301' and '20100331'
    )
    )
    alter table DERPosi_201003 add primary key clustered(TID,recGrp)
    --After table created, me apply Partitioned Views as follow,
    create view dbo.DERPosi with schemabinding
    as
    select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
    union all
    select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
    union all
    select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;
    --Me execute T-SQL as follow,
    insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(11,1123,'010','1/13/2010')
    insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(101,10123,'010','2/13/2010')
    insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(1001,100124,'10','3/27/2010')
    --Finally, I query as follow,
    select * from derposi where recGrp='3/27/2010'
    /*
    The execution plan showing Clustered Index Scan.
    */
    How to make it my Partitioned View become Clustered Index Seek?


  2. preethi Member

    Hi,
    You do not have any index having RecGrp as the first column. The clustered index is having it as a second column. So it cannot be used for search operations.
    If you change you clustered Index on RecGrp + TID (Not the other way) you will be able to get a Clustered Index Seek.
    Alternately, you can create an index on RecGrp, in your case it will be less useful.
  3. wkm1925 New Member

    tq very much sir ...[:D]
  4. preethi Member

    You are welcome. Hope the proposal helped you.

Share This Page