"james" <kush@brandes.com> wrote in message
news:usSpp22RDHA.1920@TK2MSFTNGP11.phx.gbl...
> Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using
> views. Here is the case:
>
> Table1 has Col1, Col2, Col3 .... etc.
>
> Col1 is Varchar column and has Clustered Index.
>
> When I created view
>
> V1 As select * from Table1 where Col1 like 'N%'
>
> and Then ran the statement
>
> Select * from V1
>
> I saw it is using Clustered Index SEEK on Col1 and hence result set is
> faster.
>
> But
>
> When I created view
>
> V2 As select * from Table1
>
> and Then ran the statement
>
> Select * from V2 where Col1 like 'N%'
>
> I saw it is using Clustered Index SCAN on Col1 and Filtering on 'N%',
hence
> taking longer to get result set back.
>
> My question is, Why Query Optimizer chose two seperate plan for above
case.
> As far as I know, for Optimizer, view should be no different than direct
sql
> statement. Anyone has seen this problem in Sql 7?
>
> Thanks in advance.
>
>
>

"oj" <nospam_ojngo@home.com> wrote in message
news:uXN5Jh3RDHA.940@TK2MSFTNGP11.phx.gbl...
> If you're on SP3, I suggest to update to the latest service pack. I am
> unable to reproduce it here.
>
> create table t(col1 varchar(30) primary key,col2 int)
> go
> create view v1
> as
> select * from t where col1 like 'N%'
> go
> create view v2
> as
> select * from t
> go
> set showplan_text on
> go
> select * from v1
> select * from v2 where col1 like 'N%'
> go
> set showplan_text off
> go
>
> drop view v2,v1
> drop table t
> go
>
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
>
>
> "james" <kush@brandes.com> wrote in message
> news:usSpp22RDHA.1920@TK2MSFTNGP11.phx.gbl...
> > Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using
> > views. Here is the case:
> >
> > Table1 has Col1, Col2, Col3 .... etc.
> >
> > Col1 is Varchar column and has Clustered Index.
> >
> > When I created view
> >
> > V1 As select * from Table1 where Col1 like 'N%'
> >
> > and Then ran the statement
> >
> > Select * from V1
> >
> > I saw it is using Clustered Index SEEK on Col1 and hence result set is
> > faster.
> >
> > But
> >
> > When I created view
> >
> > V2 As select * from Table1
> >
> > and Then ran the statement
> >
> > Select * from V2 where Col1 like 'N%'
> >
> > I saw it is using Clustered Index SCAN on Col1 and Filtering on 'N%',
> hence
> > taking longer to get result set back.
> >
> > My question is, Why Query Optimizer chose two seperate plan for above
> case.
> > As far as I know, for Optimizer, view should be no different than direct
> sql
> > statement. Anyone has seen this problem in Sql 7?
> >
> > Thanks in advance.
> >
> >
> >
>
>