Index on Views ...

I know that we can create indexes on views (Indexed views) in sql 2k. I am
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:

> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>