Clustered Index Scan vs Clustered Index Seek

--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?

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.

• You use SELECT * which will anyway need to use a Key lookup operation - a similar operation to Clustered Index seek
• If you have many rows returned for that RecGrp, SQL Server may chose to go with clustered index scan. Please read this article for further information: http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

tq very much sir ...[]

You are welcome. Hope the proposal helped you.